Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power BI Table Group Total

I have mock dataset looks like below. 

 

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]))
 
 
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))

 

What I want eventually is something like this. 


I hope I described my problem clear enough.
 

I would appreciate any help! 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    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
        )
    )

     

    Regards,

    Frank

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Hi Anonymous - 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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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, 


      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi Anonymous ,

         

        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
        )
    )

         

        Regards,

        Frank