cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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?

2 ACCEPTED SOLUTIONS
Resolver II

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:

Regards,

Kishore

Community Support

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.

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 4
Anonymous
Not applicable

Thanks guys! That helped me a lot 🙂

Community Support

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.

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

Helper I

Hi Thankyou your solution worked 😊

Resolver II

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:

Regards,

Kishore

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors