Return Different Measures Based On Matrix Row Values

[WinterMist]

Hello -

I have the following Matrix visual:

Rows:

- 'D SalesRep'[Name]

- 'D Customer'[Name]

- 'D SalesType'[Name]

- 'D Product'[Group Code Description]

- 'D Product'[Code]

Values (currently include 3 measures):

- [Sales Amount] = SUM('F Sales'[Sales])

- [M Contract] = [Sales Amount] * 2
- [M Direct] =

   IF(

       NOT(ISBLANK([Sales Amount])),

       ([Sales Amount] / 2) + 3

   )

NOTE: The measures for [M Contract] & [M Direct] are arbitrary.  In this case they are dummy measures, but in reality could hold any calculation.

The goal in the matrix visual is to replace [M Contract] & [M Direct] with a new measure called [Sales Based on Type], which calls these different measures based on the matrix row values.

Something like the following:

IF('D SalesType'[Name] = "Contract", [M Contract],
IF('D SalesType'[Name] = "Direct", [M Direct]))

Additionally, IF the granularity in the matrix is HIGHER than 'D SalesType'[Name] (e.g. SalesRep or Customer), THEN the totals rows should correctly calculate the sums of the different measures from the lower granularity of 'D SalesType'[Name].

Regards,

Nathan

[WinterMist]

@tamerj1 

Good catch.  So this is the entire 'F Sales' table, and there are no common products sold by both Sales Reps.

I did notice that the Total for [M Contract] is the sum of row values,

whereas the Total for [M Direct] is NOT the sum of row values.

Not sure, but I'm guessing this is because [M Contract] is additive & [M Direct] is non-additive.

Something like what Ferrari describes here: https://www.youtube.com/watch?v=6rgAkejrup8

Ferrari mentions that averages are non additive measures.

So I'm wondering if because there is a division operator in [M Direct], it causes it to be non-additive?

Regards,

Nathan

[tamerj1]

@WinterMist 
You are right. [M Direct] is non-additive. But I guess it makes sense to force additivity. 

[WinterMist]

It just hit me.  I think the SUMX iterator over 'D SalesType' does it.

If anyone has a different suggestion, let me know.

Regards,

Nathan

[tamerj1]

@WinterMist 
Good job!

For some reason the total shows 189 instead of 192. Seems there are common products sold by both salesReps. I guess they still need to be counted twice?