Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Thanks mate - shall investigate the ALLSELECTED function.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |