Different Measure for Row Totals

Question

Hi all,&nbsp;I have a measure that removes filters from an another measure using the all function on two dimensions, account, and account group.&nbsp;The measure is like so.&nbsp;ALL Last Year =VAR Calc = IF (&nbsp; &nbsp; ISBLANK ( CALCULATE([Selected Measure Total]+[Selected Measure Last Year Total], ALLEXCEPT('Date', 'Date'[Year]) ) ),&nbsp; &nbsp; BLANK (),&nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; [Selected Measure Last Year Total],&nbsp; &nbsp; &nbsp; &nbsp; ALL ( 'Account'[Account] ),&nbsp; &nbsp; &nbsp; &nbsp; ALL ( 'Account Group'[Account Group] )&nbsp; &nbsp; ))ReturnCalc&nbsp;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.&nbsp;&nbsp;I would like for the the totals for Accounts and Account groups to be the totals of all the subaccounts nested inside of them.&nbsp;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 &lt;&gt; 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.&nbsp;The most logical to me is&nbsp;ReturnIF ( HASONEFILTER ( SubAccount[SubAccount] ), Calc, SUMX ( SubAccount, Calc ) )&nbsp;But this multiplies the original incorrect (correct) value by the number of subaccounts in total selected.&nbsp;&nbsp;I have tried using combinations of keepfilters, summarize, calculatetable etc but can't seem to crack it.&nbsp;Any help would be appreciated, thanks.

lbendlin · Accepted Answer

not HASONEFILTER but HASONEVALUE,&nbsp; Other that that - yes, that is possible.&nbsp;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

Forum Discussion

Different Measure for Row Totals

1 Reply

Related content

% of total measure

Total of Measure

Difference between measure and total

Measure Total Incorrect

Different total