Replacing Subtotal Values

[Anonymous]

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 · Accepted solution]

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]

Thanks guys! That helped me a lot 🙂

[v-yangliu-msft · Accepted solution]

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

[Ayush_tiwari08]

Hi Thankyou your solution worked 😊

[Kishore_Kadhir · Accepted solution]

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