Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
jigon
Helper I
Helper I

Power BI Group Total

I have mock dataset looks like below. 

data.PNG

 

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]))
 
 
group.PNG
It populates what I want but it is including "/return" in the calculation. - I want the "group total" of group A to be $1700, not $1500. What should I change in my "group total" formula to get what I want? 

2. I want to add a column called "return" that sums all the "/return". So same group will show same amount of "return", just like "group total". 

I created measure "return" just to show you what I want. 
return = Calculate(Sum(Table1[Total Sales]),Filter(Table1,Find("/",Table1[Item code], ,0)<>0))
return.PNG

 

What I want eventually is something like this. 

answer.PNG


I hope I described my problem clear enough.
 

I would appreciate any help! 

1 ACCEPTED SOLUTION

@jigon  - 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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
jigon
Helper I
Helper I

@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. 

@jigon  - 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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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. 

 

deduction.PNG

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. 

group total.PNG

Do you think you need the original pbix file? 

@GilbertQ @v-danhe-msft Could you help on solving problem above? 

Thank you

Hi there did you try what Greg suggested?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@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.

 

deduction not correct.PNG

 

 

 

But when I unfilter the itemcode to show everything, it shows the correct amount. 



deduction correct.PNG

 

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. 

breakdown.PNG

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? 

@Greg_Deckler Greg, Any idea for my problem above?

PBIX would help but I'm thinking that you need to modify your ALLEXCEPT to include customer since you have a customer slicer?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Is there a way that I can send you the pbix file securely? 

@Greg_Deckler Thank you so much!! This works perfectly! I appreciate your help!! 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors