SUM with FILTER and ALLSELECTED

[Pbi07]

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

[Vera_33]

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

[Pbi07]

Thanks @Vera_33 

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 

Account	Location	Total Amount	Last Year Total	% of Sales
Materials	EU	$95,325	$17,325	0
Packing	AP	$55,000		0
Packing	US	$16,690	$19,420	0

[Vera_33]

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

)

[parry2k]

@Pbi07 it is not clear what you are looking for, given the example what is your expected output, explain what is working and what is not working?

[jdbuchanan71]

@Pbi07 

Is this what you are looking for?

I just changed the sales measure to ignore the selected account (on the row) and always return the amount for sales.

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

[amitchandak]

@Pbi07 ,Try like

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

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

[Pbi07]

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

Journal	Fiscal Year	Fiscal Period	Account	Location	Period end Date	Amount
5150	2019	9	Materials	EU	1/31/2019 0:00	$1,800.00
5870	2019	12	Materials	EU	4/30/2019 0:00	$15,525.00
10202	2020	1	Materials	EU	5/31/2019 0:00	$48,000.00
34842	6060	36	Materials	EU	4/30/2020 0:00	$47,325.00
11440	2020	11	Packing	AP	3/31/2020 0:00	$55,000.00
5209	2019	10	Packing	US	2/28/2019 0:00	$1,720.00
5590	2019	12	Packing	US	4/30/2019 0:00	$17,700.00
10275	2020	3	Packing	US	7/31/2019 0:00	$16,690.00
90020	2020	11	Sales	EU	3/31/2020 0:00	$428,868.00
80006	2019	11	Sales	US	3/31/2019 0:00	$185,000.00
90001	2020	10	Sales	US	2/29/2020 0:00	$188,111.00

[amitchandak]

@Pbi07 , need expected output. Check if this can help

https://www.dropbox.com/s/k0opque79mpx0n6/sales_percent.pbix?dl=0

[Pbi07]

@amitchandak @parry2k @jdbuchanan71 

Apologies for not providing the expected result.

Account	Location	Total Amount	Last Year Total	% of Sales
Materials	EU	$95,325	$17,325	15%
Packing	AP	$55,000		9%
Packing	US	$16,690	$19,420	3%

% of sales = Total Amount / 616979 .  616979 is the total of "Sales" for 2020 FY. 

[parry2k · Accepted solution]

@Pbi07 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.⚡