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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jigon
Helper I
Helper I

Power BI Table 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

Hi @jigon ,

 

To update the formula as below.

 

return =
VAR __group =
    MAX ( 'Table1'[Group] )
VAR _date =
    SELECTEDVALUE ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Total Sales] ),
        FILTER (
            ALL ( Table1 ),
            FIND ( "/", Table1[Item code],, 0 ) <> 0
                && [Group] = __group
                && 'Table1'[Date] = _date
        )
    )

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Hi @jigon - Perhaps try this measure and then filter out /return in a Visual filter:

 

return = 
VAR __group = MAX('Table17'[Group])
RETURN
Calculate(Sum(Table17[Total Sales]),Filter(ALL(Table17),Find("/",Table17[Item code], ,0)<>0 && [Group]=__group))

See Page 5, Table17 of attached. 


@ 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 

Thank you Greg! 

But I found couple of problem. 

1) The group total still includes the return amount. 

 

2) When I filter by date (using slicer) and choose February, I still see the "return" amount showing -200 and -300. But if you look at the dataset, there is no return in February. 

Is there a workaround? 

 

Thank you, 


Hi @jigon ,

 

To update the formula as below.

 

return =
VAR __group =
    MAX ( 'Table1'[Group] )
VAR _date =
    SELECTEDVALUE ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Total Sales] ),
        FILTER (
            ALL ( Table1 ),
            FIND ( "/", Table1[Item code],, 0 ) <> 0
                && [Group] = __group
                && 'Table1'[Date] = _date
        )
    )

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @jigon ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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