Filtered average

[Kevboy_telford]

Morning

I am new to Power BI but trying to build a Top 10 Margin Rank (both good and bad) however I want to only include those products where we have sold at least £100 in the selected time period (to try and get rid of any noise of single items being sold as a lost lead)

Main table has SKU, margin value and sales values so currently using the following measures (which isnt working as expected...)

total sales ex vat   = sum('solditems'[sale_value])
total margin ex vat  = sum('solditems'[margin_value])
Filtered avg margin  = CALCULATE(DIVIDE([total margin ex vat],[total sales ex vat]),FILTER('solditems',[total sales ex vat]>100))
Margin rank sales Good  = RANKX(ALLSELECTED('solditems'[SKU]),([Filtered avg margin]),,desc,Dense)
Margin rank sales Bad  = RANKX(ALLSELECTED('solditems'[SKU]),([Filtered avg margin]),,asc,Dense)

All I get back is 2 items and I expected at least 100!

NB to do it in SQL is
select
 SKU,
 avg(margin_value/sale_value) as margin_ratio,
 sum(sale_value)     as total_sales,
 count(*)      as amount
from solditems
where sale_value>0
group by SKU
having sum(sale_value)>100

[Anonymous]

Hi @Kevboy_telford，

I am not sure what is your expected result. I make a simple test using the following sample data.

Firstly, create two new tables using DAX below.

Table = SUMMARIZE(solditems,solditems[SKU],"sumMari",SUM(solditems[margin_value]),"sumsales",SUM(solditems[sale_value]))
Table2 = CALCULATETABLE('Table','Table'[sumsales]>100)

Secondly, create new columns in Table2 using the DAX below.

filter avg margin = 'Table2'[sumMari]/'Table2'[sumsales]
Margin rank sales Good = RANKX('Table2',(Table2[filter avg margin]),,desc,Dense)
Margin rank sales Bad = RANKX(Table2,(Table2[filter avg margin]),,asc,Dense)

If the above DAX formulas don't help, please share sample data of your table and post expected result  here.

Thanks,
Lydia Zhang

[Kevboy_telford]

Thanks Lydia

Its really close to what I want  --  just need to be able to filter on a date (which I realised I didnt mention at the beginning) - basically letting users slice a date to find out which items were best/worse performing (but only showing those that sold over £100 in that period)

example data

thanks for your time on this

cheers

Kevin

[Anonymous · Accepted solution]

Hi @Kevboy_telford,

In this scenario, just reserve your measures posted in your orginal post, just change the DAX formula of Filtered avg margin into:

Filtered avg margin = IF([total sales ex vat]>100, [total margin ex vat]/[total sales ex vat],BLANK())

And create a Date column using the following DAX and create a slicer using the Date field.
Date = DATE(YEAR(solditems[SoldDate]),MONTH(solditems[SoldDate]),DAY(solditems[SoldDate]))

At last, set Filtered avg margin in Visual level filters to "not blank".

Thanks,
Lydia Zhang