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

Get 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

Reply
Anonymous
Not applicable

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

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

 

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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

 

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Anonymous
Not applicable

@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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Anonymous
Not applicable

Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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