Grand Total of measure - hasonefilter doesn't work...

[JT-DLPLC]

Hello everybody, hoping you can help. My totals don’t add up… Am sure this is a common problem, but the solutions I’ve found elsewhere (including the HASONEFILTER) don’t seem to work for me.

I am doing some supplier rebate tracking – if we buy more than x amount of goods in a year then we get a rebate y% on the total purchase amount.

Order Value YTD is just the YTD SUM on the purchase facts.

Full Year Target Amount is just the Full Year Sum on the Target Facts

DTG Full Year Target is Full Year Target – Order Value YTD

Rebate % is Max(rebate) on the rebate table

Rebate Test 1:=if([DTG Full Year Target]>0,0,(sumx('Purchase Facts',[GBP Order Value YTD]*[Rebate])))

Rebate Test 2=(calculate(sumx('Purchase Facts',[GBP Order Value YTD]*[Rebate]),filter('Purchase Facts',[DTG Full Year Target]<0)))

We can see that Rebate test 1 gives the correct amounts for the indivudal suppliers but not the grand total. Rebate test 2 just throws out blanks.

 

How can I get the total to be the sum of the individual suppliers? i.e. 213,665 + 64062 + 0 = 277727

Any help greatly appreciated.

[Greg_Deckler]

For many of these kinds of issues, use HASONEFILTER combined with an ALLSELECTED in the case of your total. Very tough to say exactly without more information.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[JT-DLPLC]

Thanks mate - shall investigate the ALLSELECTED function.