March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |