Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SUM with FILTER and ALLSELECTED

Having trouble with my DAX. 

Table contains different Account charges for different periods. 

Having a measure to calculate the amont only for the Sales and calculating the % for each account against the sales . 

But, the total is going wrong for the below measure. This is my first attempt with ALL & ALLSELECTED. 

 

Sales Total =
CALCULATE( SUM('Sales'[Amount] ),
FILTER( ALL('Sales'),'Sales'[Account] = "Sales"),
ALLSELECTED( 'Calendar'[FiscalYearNum] )
)
 

Pbix is  https://drive.google.com/file/d/19MJftynUVzV9BVfU3RuV0RLeKHSJ6K_G/view?usp=sharing

 

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous change sales total measure to this and everything will work

     

    Sales Total = 
CALCULATE( SUM('Sales'[Amount] ),
    'Sales'[Account] = "Sales",
    ALLSELECTED ( Sales )
)

     

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

     

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, it seems you want slicer filter to flow into your Total Sales, try it:

     

    Sales Total 2:=CALCULATE( SUM('Sales'[Amount] ),
    FILTER( ALL('Sales'[Account]),'Sales'[Account] = "Sales")
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Anonymous 

       

      That works for the total with the below 

      Sales Total 2:=CALCULATE( SUM('Sales'[Amount] ),
      FILTER( ALL('Sales'[Account]),'Sales'[Account] = "Sales")
      ) 

      % of Sales = divide(
      [Total Amount],[Sales],0)

      But, when i use this total to find the % of Sales it gives 0 for all the rows. That was the reason i was trying to do the ALLSELECTED. 

      What am i missing 

      AccountLocationTotal AmountLast Year Total% of Sales
      MaterialsEU$95,325$17,3250
      PackingAP$55,000 0
      PackingUS$16,690$19,4200

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Oh...I see, the % is a little bit tricky...you want to get the sales amount but keep the slicer filter flowing into the denominator as well? Try this:

        Sales Total 2=

        VAR CurYear = SELECTEDVALUE('Date'[Year],ALL('Date'[Year]) )

        RETURN

        CALCULATE( SUM('Sales'[Amount] ), FILTER(ALL(Sales), Sales[Year]=CurYear && Sales[Account]="Sales")

        )

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,Try like

    Sales Total =
    CALCULATE( SUM('Sales'[Amount] ),All('Sales'))

     

    % = divide( SUM('Sales'[Amount] ),[Sales Total])

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak  

       

      The total is only for the [Account] = "Sales".  My slicer is Fiscal Year.  I have the % calculation working. My date table has relation set to the Period End Date. 

       

      JournalFiscal YearFiscal PeriodAccountLocationPeriod end DateAmount
      515020199MaterialsEU1/31/2019 0:00$1,800.00
      5870201912MaterialsEU4/30/2019 0:00$15,525.00
      1020220201MaterialsEU5/31/2019 0:00$48,000.00
      34842606036MaterialsEU4/30/2020 0:00$47,325.00
      11440202011PackingAP3/31/2020 0:00$55,000.00
      5209201910PackingUS2/28/2019 0:00$1,720.00
      5590201912PackingUS4/30/2019 0:00$17,700.00
      1027520203PackingUS7/31/2019 0:00$16,690.00
      90020202011SalesEU3/31/2020 0:00$428,868.00
      80006201911SalesUS3/31/2019 0:00$185,000.00
      90001202010SalesUS2/29/2020 0:00$188,111.00