Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

GilbertQ v-danhe-msft Could you help on solving problem above? 

Thank you

GilbertQ's avatar
GilbertQ
Super User
7 years ago
Hi there did you try what Greg suggested?
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

     

     

     

     

    But when I unfilter the itemcode to show everything, it shows the correct amount. 



     

    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. 



    Please let me know if anything is not clear. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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