Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello - Our ERP system spits out a column called "Total Price". What is happening is that the total price is assigned to each line when it gets into Power Bi and ends up like this....which gives me incorrect totals. Even if I remove the Order Line from the matrix, I still get the 5,042 total. What I am trying to achieve is to have the correct total show, which should be the 840,420. I've tried HASONEVALUE but with no luck.
Solved! Go to Solution.
@Greg_Deckler Hi Greg - I worked out a simple solution that seems to work. Unfortunately (and surprisingly!) your solution didn't work. I called yours the New New Sum.
But here is the one that worked:
@Greg_Deckler Hi Greg - I worked out a simple solution that seems to work. Unfortunately (and surprisingly!) your solution didn't work. I called yours the New New Sum.
But here is the one that worked:
@texmexdragon2 Glad you got it! One nit-pick, I wouldn't use VALUES but rather DISTINCT. Unless you want that blank row that VALUES can return.
@texmexdragon2 Try this:
New Sum of Total Price = IF(HASONEVALUE(ShippedOrdersALL_Query[Order Number]),[Sum of Total Price],AVERAGEX(VALUES(ShippedOrdersALL_Query[Order Number]),[Sum of Total Price]))
User | Count |
---|---|
93 | |
85 | |
77 | |
67 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |