Help with a calculation that changes based on several criteria

[KBedor]

I need a way to calculate revenue where the formula varies based on several criteria from different columns in the Business Unit table. The calculation uses different combination of revenue classifications from accounting categories. The combinations are:

Revenue calculation variations:

1. SUM (R010 + R030) *-1
2. B) SUM (R010 *-1)
3. C) SUM (R020 + R030) *-1
4. D) SUM (R030 *-1)
5. E) SUM (R010 + R020 + R030) *-1

For the most part, the calculations can be applied at the most granular level of the Business Unit table hierarchy.   See table below for example:

Note that GHI has 2 rows using the same subset so finance can show 2 types of revenue and  JKL 1 used a second time to apply transfer revenue to another unit.  

Business Unit	Sub Unit	Amount
ABC	ABC 1	Revenue A
ABC	ABC 2	Revenue B
ABC	ABC 3	Revenue C
DEF	DEF	Revenue A
GHI	GHI 1	Revenue B
GHI	GHI 1*	Revenue D
JKL	JKL 1	Revenue B
Other	JKL 1*	Revenue D
Other	Other	Revenue E

I'm not even sure this can be done.  Any suggestions would be greatly appreciated.

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a sample to test:

Then try this measure:

Measure = 
var R010 = CALCULATE(SUM(R[Value]),FILTER(R,R[ID]="R010"))
var R020 = CALCULATE(SUM(R[Value]),FILTER(R,R[ID]="R020"))
var R030 = CALCULATE(SUM(R[Value]),FILTER(R,R[ID]="R030"))
return
SWITCH(MAX('Table'[Amount]),"Revenue A",(R010+R020)*-1,"Revenue B",R010*-1,"Revenue C",R020+R030,"Revenue D",R030*-1,"Revenue E",(R010+R020+R030)*-1)

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[tctrout]

I dont have a direct answer, but I can point you in the right direction.

You need a key column, I am going to assume it is your Sub Unit column.

Build a table visual that lists your Key value identified above, I am assuming Sub Unit.

Utilize the Switch() command to evaluate the Key and execute the desired measure created above.  

Psuedo DAX of concept:

DynamicSwithcMeasure = 

var KeyColumn = SELECTEDVALUE(Table[KeyField]

var Measure1 = SUM (R010 + R030) *-1

var Measure2 =  SUM (R010 *-1)

var Measure3 = SUM (R020 + R030) *-1

var Measure4 = SUM (R030 *-1)

var Measure5 = SUM (R010 + R020 + R030) *-1

return

SWITCH(KeyColumn

    ,"ABC 1" , Measure1

    ,"ABC 2", Measure2

    ,"ABC3", Measure 3

   ....etc continue patttern

,0)

Note that this will create an iterative process and doing this on small datasets is ideal.  If your data is large, monitor for performance degredation.  

[KBedor]

This gets me most of the way there.  The challenge is that the measure can apply to the same key, sub unit in my example, in two different ways.  The combination of sub unit and measure determines how the returned value rolls up in the hierarchy. This is driven by how finance assigns different types of revenue to different P&L’s for financial statements. 

I created a table that basically replicates the org structure, added rows for the sub units to roll-up to different P&L’s, then added an index column and a column indicating which calculation to use.  I joined this table to the fact table by the subunit – the only option. It’s a bi-directional cross filter with many to many relationship. 

When I test my measure in a matrix visual, it’s only showing totals for some rows at the highest level of the hierarchy, the ones that don’t have more than one calculation for a subunit.  When I drill down to the next level, it’s correct, though I would prefer to also see rows at this level if is the value is 0.  I’m completely stuck.  Unfortunately this is a base measure, critical for getting everything else correct in the dashboard.

[KBedor]

I should have been more specific, @tctrout your suggestion is very close.