cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fraze
Regular Visitor

Subtotal Needs to Calculates Differently than Row Level

Hello,

 

I have a matrix using a measure with the DAX expression 'Expense / Sales'.

 

So each row represents a % of total sales. see below:

 

Fraze_1-1618876763568.png

 

Currently my subtotal for the 3 year average would be 30.25%; however, I want my row-level sub-total to be the inverse of what it currently is. So the individual rows are correct, but the subtotal needs to be (1 - 30.25%) = 69.75%

 

How can I modify by DAX expression so it calculates the sub-total slightly different than the rest of the table.

 

Thanks 

  

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @Fraze

 

You can use HASONEVALUE to modify the calculation logic of  subtotal.

Try measure as:

IF(
    HASONEVALUE('Table'[Column]),
    measure,
    new measure    //calculation logic of '69.75%'
)

 

Best Regards,

Link 

 

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

5 REPLIES 5
v-xulin-mstf
Community Support
Community Support

Hi @Fraze

 

You can use HASONEVALUE to modify the calculation logic of  subtotal.

Try measure as:

IF(
    HASONEVALUE('Table'[Column]),
    measure,
    new measure    //calculation logic of '69.75%'
)

 

Best Regards,

Link 

 

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

jonoling
Frequent Visitor

Hi Fraze,

 

You could use the ISFILTERED() function to determine if the year is being filtered, and calculate the measure accordingly. My example assumes your data is structured as below:

jonoling_0-1618881738218.png

 

With the DAX function:

 

COGS% = 

AVERAGEX(
    'Table',
    IF(
        ISFILTERED('Table'[Year]),
        DIVIDE('Table'[Expenses],'Table'[Sales]),
       1 - DIVIDE('Table'[Expenses],'Table'[Sales])
    )
)

 

 

It returns the following result:

jonoling_1-1618881994090.png

If you have other slicers or filters affecting the Year field, then you may need to use a logical rule such as COUNTROWS(DISTINCT()) or similar to calculate the 3 year scenario.

 

Edit: Tweaked the function to use the safe DIVIDE function rather than slashes.

Fraze
Regular Visitor

Thanks for the information.

 

Unfortunatly, my date does have some filters in the background. In addition to what you already mentioned, Any further insight on how to best deal with that. 

 

Thanks

jonoling
Frequent Visitor

Hi Fraze,

 

Just following up to see if this solved your issue?

jonoling
Frequent Visitor

You could use DISTINCTCOUNT('Table'[Year]) > 1 instead of the ISFILTERED('Table'[Year]) statement in the above function and that should still work assuming you want any non-individual subtotals to show the inverse side.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors