Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Replacing Subtotal Values

Hey guys,

 

I have a question regarding subtotals of a Matrix. Below I have some example data that is similar to my specific case. I tried to find a suitable DAX function but could not find anything that matches.

Basically I want to know, if it is possible to replace the Subtotal values in the below Matrix either by a formula or by a specific value (which I get from a different column). 

For example: I do not want the Value for BMW 1 (51,000) to be summed up by the individual entries, but instead I want to either

1) plug in a value that I get from another column from the same Excel Sheet (for example 20,000)

2) or change the formula that calculates the subtotal (for example I want the square root of (22,000 + 29,000).

 

Does anyone know if that is possible with any DAX measure?

  • Kishore_Kadhir's avatar
    Kishore_Kadhir
    4 years ago

    Hi Anonymous ,

     

    You can use IF condition to check if the matrix is filtered by brand/make. If yes, you can fill it up with some other value.

     

    Sample DAX:

    IF(ISINSCOPE([Brand/Make], (Add your new value here), [Price])

    Regards,

    Kishore

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Measure_Result1 =
IF(
   HASONEVALUE('Table'[Group1])&&NOT(HASONEVALUE('Table'[Group2]))&&MAX('Table'[Group1])="1"
 ,
 CALCULATE(SUM('Table'[Column_Amount]),FILTER(ALL('Table'),
 'Table'[Brand]="BWM"&&'Table'[Group1]=MAX('Table'[Group1])&&'Table'[Group2]=MAX('Table'[Group2])))
 ,MAX('Table'[Price]))


    Measure_Result2 =
IF(
   HASONEVALUE('Table'[Group1])&&NOT(HASONEVALUE('Table'[Group2])),
    SQRT( SUMX(
      FILTER(ALL('Table'),'Table'[Brand]=MAX('Table'[Brand])&&'Table'[Group1]=MAX('Table'[Group1])),[Price])),MAX('Table'[Price]))

    2. Result.

    If you need pbix, please click here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Measure_Result1 =
IF(
   HASONEVALUE('Table'[Group1])&&NOT(HASONEVALUE('Table'[Group2]))&&MAX('Table'[Group1])="1"
 ,
 CALCULATE(SUM('Table'[Column_Amount]),FILTER(ALL('Table'),
 'Table'[Brand]="BWM"&&'Table'[Group1]=MAX('Table'[Group1])&&'Table'[Group2]=MAX('Table'[Group2])))
 ,MAX('Table'[Price]))


    Measure_Result2 =
IF(
   HASONEVALUE('Table'[Group1])&&NOT(HASONEVALUE('Table'[Group2])),
    SQRT( SUMX(
      FILTER(ALL('Table'),'Table'[Brand]=MAX('Table'[Brand])&&'Table'[Group1]=MAX('Table'[Group1])),[Price])),MAX('Table'[Price]))

    2. Result.

    If you need pbix, please click here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Kishore_Kadhir's avatar
    Kishore_Kadhir
    Resolver II
    4 years ago

    Hi Anonymous ,

     

    You can use IF condition to check if the matrix is filtered by brand/make. If yes, you can fill it up with some other value.

     

    Sample DAX:

    IF(ISINSCOPE([Brand/Make], (Add your new value here), [Price])

    Regards,

    Kishore

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks guys! That helped me a lot 🙂