Different Measure for Row Totals

AC4 — Tue, 16 Apr 2024 11:45:53 GMT

Hi all,

I have a measure that removes filters from an another measure using the all function on two dimensions, account, and account group.

The measure is like so.

ALL Last Year =

VAR Calc =

IF (

ISBLANK ( CALCULATE([Selected Measure Total]+[Selected Measure Last Year Total], ALLEXCEPT('Date', 'Date'[Year]) ) ),

BLANK (),

CALCULATE (

[Selected Measure Last Year Total],

ALL ( 'Account'[Account] ),

ALL ( 'Account Group'[Account Group] )

)

Return

Calc

This works as intended on the level below accounts, sub accounts, and allows me to see if there was any "selected measure total" for last year outside of that account/account group for that sub account. But when adding this measure to a matrix it obviously totals it incorrectly for me (correctly as per the logic) for Accounts and Account Groups.

I would like for the the totals for Accounts and Account groups to be the totals of all the subaccounts nested inside of them.

I followed the advice of this thread https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376 to use HASONEFILTER to alter the result if subaccount <> have one filter, but my problem is I cannot get the alternate formula for when it does not have a filter, to work as intended.

The most logical to me is

Return

IF ( HASONEFILTER ( SubAccount[SubAccount] ), Calc, SUMX ( SubAccount, Calc ) )

But this multiplies the original incorrect (correct) value by the number of subaccounts in total selected.

I have tried using combinations of keepfilters, summarize, calculatetable etc but can't seem to crack it.

Any help would be appreciated, thanks.

Re: Different Measure for Row Totals

lbendlin — Tue, 16 Apr 2024 21:48:35 GMT

not HASONEFILTER but HASONEVALUE, Other that that - yes, that is possible. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Different Measure for Row Totals in DAX Commands and Tips

Different Measure for Row Totals

Re: Different Measure for Row Totals