Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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! 

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

    Hi Anonymous ,

    I have modified my formula as below, you could have a try:

    Measure = CALCULATE(SUM(Table1[Total Sales]),FILTER(ALL('Table1'),'Table1'[Group]=MAX('Table1'[Group])&&'Table1'[item code]<>"/return"))
    Measure 2 = CALCULATE(SUM(Table1[Total Sales]),FILTER(ALLEXCEPT(Table1,Table1[Group],Table1[Date]),'Table1'[item code]="/return"))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

3 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below formula:

    Measure = SUMX(CALCULATETABLE('Table1',FILTER(ALL(Table1),'Table1'[Group]=MAX('Table1'[Group]))),CALCULATE(ABS(SUM(Table1[Total Sales]))))
    Measure 2 = CALCULATE(SUM(Table1[Total Sales]),FILTER(ALLEXCEPT(Table1,Table1[Group]),'Table1'[item code]="/return"))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Daniel, 

      Your solution has two problems. 

      1. "Measure" doesn't total up correctly. It should be 1700 (800+600+300) for group A. 

      2. "Measure2" doesn't reflect the date slicer. If I filter to February, Measure 2 still has values. But if you look at the dataset, February doesn't have "/return" value. 

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous ,

        I have modified my formula as below, you could have a try:

        Measure = CALCULATE(SUM(Table1[Total Sales]),FILTER(ALL('Table1'),'Table1'[Group]=MAX('Table1'[Group])&&'Table1'[item code]<>"/return"))
        Measure 2 = CALCULATE(SUM(Table1[Total Sales]),FILTER(ALLEXCEPT(Table1,Table1[Group],Table1[Date]),'Table1'[item code]="/return"))

        Result:

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He