Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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).

Example_Pic.PNG

 

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

2 ACCEPTED SOLUTIONS
Kishore_Kadhir
Resolver II
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:

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

Regards,

Kishore

View solution in original post

v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

I created some data:

vyangliumsft_0-1657608622561.png

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.

vyangliumsft_1-1657608622566.png

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thanks guys! That helped me a lot 🙂

v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

I created some data:

vyangliumsft_0-1657608622561.png

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.

vyangliumsft_1-1657608622566.png

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

Hi Thankyou your solution worked 😊

Kishore_Kadhir
Resolver II
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:

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

Regards,

Kishore

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.