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.

     

     

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper 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.