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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.