Forum Discussion

WinterMist's avatar
WinterMist
Impactful Individual
3 years ago

Return Different Measures Based On Matrix Row Values

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

4 Replies

  • WinterMist's avatar
    WinterMist
    Impactful Individual
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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?

  • WinterMist's avatar
    WinterMist
    Impactful Individual
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

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