Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have mock dataset looks like below.
On my visualization, I have two problems:
1) I am trying to show the total sales by group. - called "group total". But on my dataset, I have Item Code called "/return". I do not want to list "/return" on my visualization. So I filtered it out through filters.
I created measure called "group total" with the following dax formula :
group total = CALCULATE(SUM(Table1[Total Sales]),ALLEXCEPT(Table1,Table1[Group],Table1[Date]))
return = Calculate(Sum(Table1[Total Sales]),Filter(Table1,Find("/",Table1[Item code], ,0)<>0))
What I want eventually is something like this.
I hope I described my problem clear enough.
I would appreciate any help!
Solved! Go to Solution.
@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
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?
@GilbertQ
Yes.
But I found 2 more problems
1.
Greg's version of "deduction" didn't work. So I modified this to get it work.
deduction = Calculate(SUM(AR_InvoiceHistoryDetail[Total Sales]),
Filter(ALLEXCEPT(AR_InvoiceHistoryDetail,AR_InvoiceHistoryDetail[CustomerNo],
AR_InvoiceHistoryDetail[Invoice Date]),
find("/",AR_InvoiceHistoryDetail[ItemCode], ,0)<>0))
But this doesn't show the amount that I want.
But when I unfilter the itemcode to show everything, it shows the correct amount.
This is exactly what I want (group total and deduction value-wise), but I don't want to show the itemcodes that have "/" in the beginning.
2. when I filter by customer through slicer, everything both group total and deduction breaks down. I want them to keep the same amount even when they are filtered through slicer.
Please let me know if anything is not clear.
@v-danhe-msft This is pretty much the same problem as the other post. I applied the code to my real data, and I am having these problems. Could you help, please?
PBIX would help but I'm thinking that you need to modify your ALLEXCEPT to include customer since you have a customer slicer?
@Greg_Deckler Thank you so much!! This works perfectly! I appreciate your help!!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |