Forum Discussion
Power BI Group Total
Anonymous - How about these:
return = VAR __group = MAX('Table17'[Group]) RETURN CALCULATE(SUM(Table17[Total Sales]),Filter(ALLEXCEPT(Table17,Table17[Date]),[Item code]="/return" && [Group]=__group))group total = CALCULATE(SUMX(FILTER(Table17,[Item code]<>"/return"),[Total Sales]),ALLEXCEPT(Table17,Table17[Group],Table17[Date]))
Very minor tweaks.
Attached again, Page 5, Table 17.
Greg_Deckler I am not sure why my previous post got deleted... So I posted again.
My problem with your solution was that
1) The group total still includes the "/return" amount
2) If I filter by date using slicer, february still shows "/return". But if you look at the dataset, February doesn't have return amount.
Anonymous - How about these:
return = VAR __group = MAX('Table17'[Group]) RETURN CALCULATE(SUM(Table17[Total Sales]),Filter(ALLEXCEPT(Table17,Table17[Date]),[Item code]="/return" && [Group]=__group))group total = CALCULATE(SUMX(FILTER(Table17,[Item code]<>"/return"),[Total Sales]),ALLEXCEPT(Table17,Table17[Group],Table17[Date]))
Very minor tweaks.
Attached again, Page 5, Table 17.
Greg_Deckler Thank you so much!! This works perfectly! I appreciate your help!!
Greg_Deckler
Sorry to bother you again, but I applied the code to my real data, and found 2 problems.1) the "deduction" (which is same with "return" in my example) seems not working. The correct amount should be -3097.26 for customer ACEHW01. But it seems like it is all broken down? And the total number doesn't match. I screen captured my code so that you can take a look.
2) When I filter by customer using slicer, the group total doesn't work anymore. I chose CustomerNo "ACEHW01" as an example. and the group total breaks as well. The total seems correct though. I screencaptured my code for grouptotal as well.
Do you think you need the original pbix file?