Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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! 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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.

     

     

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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. 

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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