Returning cost?

[Lebene]

Hi everyone,

Perhaps not drinking water is affecting me or this is just a formula I've not learned yet!

I'm trying to begin my journey into learning PowerBI and with that I'm reworking an existing spreadsheet to be PowerBi uploading friendly.

I'll try to explain this simply:

I have different columns and I basically want the 'Cost' column to automatically show the correct price per head average which I have in a different table on a different sheet when all the fields are filled in - the variable that changes the price is the 'Cost tier'. So when the Skill code is entered, it creates a conditioned drop-down for the Session column, the session code auto-populates from the name of the session. I guess I could make the f2f and virtual column auto-populate too but I dont want to add any more formulas to it.

Skill	F2F or virtual?	Session	Session code	Many columns in between	Cost tier	Cost
this has the name for what type of workshop it is (for example, was it for managers or an org-wide opportunity? This has three drop-down options	Is it delivered face to face or virtually?​	The workshop name	The workshop code		There is the Base tier which is basically the "standard price" and then tier 2, 3,4... etc for difference discount bands in a drop-down.

What I got so far is just a nested IF/S but I couldn't get it past the first condition:
=IFS([@[Core Skill]]="SKILL1",IFS([@[F2F or Virtual]]="F2F",IF([@[Cost tier]]="Base",INDEX(Costs[Base Cost per head],MATCH([@[Session code]],Costs[Session code],0))))) = shows the correct price fine but only for this condition, I might as well have just done a index(match based on session code. Oh, wait, if I combine the workshop code and the index(match this should work? It's like my last living brain cells are trying so hard for me to have a revelation.

If I need to use some form of LOOKUP function outside of index(match or vlookup, I'm totally hopeless at this stage.

I've also used up my brain juices today with no water consumption.

Thanks, everyone for any insight, I feel like I'm missing something gapingly obvious.

 

[Lebene]

If there anyone who might know what I need to do? I've been Googling endlessly but in this instance, I've run dry.

After having a think: I need to return a cost based on 2 criteria but the cost can change based on the discount tier that it's on (which is one of the three criteria).

So far I've only achieved it by nested IF:

=IF([@[Cost tier]]="Base",INDEX(Costs[Base Cost per head],MATCH([@[Session code]],Costs[Session code],0)),IF([@[Cost tier]]="Tier 2",INDEX(Costs[Tier 2 Cost per head],MATCH([@[Session code]],Costs[Session code],0)),IF([@[Cost tier]]="Tier 3",INDEX(Costs[T3 Cost per head],MATCH([@[Session code]],Costs[Session code],0)))))