topic Re: Replacing Subtotal Values in DAX Commands and Tips

Replacing Subtotal Values

Anonymous — Fri, 08 Jul 2022 16:04:17 GMT

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?

Re: Replacing Subtotal Values

Kishore_Kadhir — Sat, 09 Jul 2022 03:32:35 GMT

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

Re: Replacing Subtotal Values

Anonymous — Tue, 12 Jul 2022 06:51:08 GMT

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

Re: Replacing Subtotal Values

Anonymous — Fri, 15 Jul 2022 08:26:29 GMT

Thanks guys! That helped me a lot 🙂

Re: Replacing Subtotal Values

Ayush_tiwari08 — Fri, 10 Nov 2023 15:43:05 GMT

Hi Thankyou your solution worked 😊