cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors