Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.