How to apply a different measure based on column value?

[vedantsri]

Hi All,

I have a table that looks like this:

Office	Tier
A	High
B	Mid
C	Mid
D	Low

Office column is a distinct column.

Now I wanted to calculate Demand (Which is coming from a separate demand table) for each of these offices.

The demand would be calculated diffeently based on the 'Tier' of the office.

So now I have 3 measure that I created to calculate the demand for each office depending on the value of the Tier column in the office table mentioned above (One measure for each Tier type; High, Mid, Low). I also have a slicer for the 'Office' column in my Office table for a user to select the office they want the demand for. If multiple offices are selected then we would get the sum of the calculated demand for each of those offices.

I wanted to create a 4th measure that I would use in a visual that is able to display the demand of an office or the sum of demand of multiple selected offices. This measure however would dynamically calculate the value of the demand (using my 3 created measures) based on the 'Tier' value in the office table mentioned above. How should I do that?

Thanks,

Vedant

[KNP]

Could you post your existing measures and if possible, a screen shot of what the visuals currently look like?

[vedantsri]

Hi KNP,

Thank you for responding!

The three measures I created are similar to this one:

HighTierDemandNeeded =

 VAR DemandDeficit = [Supply]>2*[Demand]

var tier2 = CALCULATE([Supply]-2*[Demand])

return

if(DemandDeficit,tier2,0)

Similarly, 

MidTierDemand =

 VAR DemandDeficit = [Supply]>1*[Demand]

var tier2 = CALCULATE([Supply]-1*[Demand])

return

if(DemandDeficit,tier2,0)

and so on for Low tier. 

The [Demand] measure that I have used in these measures is just a simple summation measure on a Column from a separate Demand table that I have connected to my Office table using the Office column. 

So now I want a visual that shows the right measure value (between hightierDemand, midtierdemand, lowtierdemand) based on the 'Tier' of the office selected in my slicer.

[KNP]

I think I have an understanding of what you're trying to do, just need a little more info.

What is the [Supply] measure?

It would help to have some sample data from the demand table if possible.

[vedantsri]

A part of the Demand table looks like this:

Office	Date	Needed
A	03/11/2023	5
B	03/09/2023	4
A	03/09/2023	6
C	03/06/2023	4
B	03/07/2023	6
A	03/13/2023	7

So the Demand is just summing the 'Needed' column for each office in this Demand table. I Have this connected to my office table on the Office column.

The [Supply] measure if similar but is working on the 'Supply' table.

A snippet of the Supply table:

Item	ItemCreationDate	Office
1111	[Some Date]	A
1112	[Some Date]	B
1113	[Some Date]	C
1114	[Some Date]	A
1115	[Some Date]	C

So the [Supply] measure is just supposed to do a count(Item) for each office basically. So in this snippet, Office A would have the value 2, B=1 and c=2. I have this Supply table connected to my original office table on the 'Office' column.

Thanks.

[KNP]

Am I correct in assuming this is your numbering for tier in the measures? 

[KNP]

@vedantsri,

Above I made an assumption about the numbering of low, medium, high and added a column to the Office table in Power Query.

If this is incorrect I'll need to update.

Attached is a very basic PBIX file using your sample data, altered slightly to ensure one crosses the threshold.

Have a look and see how close the measure called 'Measure' is, to what you're looking for?