Show only lowest average per category with filters

[MichelDias]

Hi friends!

We have a list of quotes of services, where a client can receive in the same quote a list of services available with a variable price, and not available services are returned with a price of zero. There are other information on each quote, such as the Quote Date (Format dd/mm/yyyy) and the State from where the client has made the quote, here is a sample of a quote:

QuoteNumber	QuoteDate	Service	Service Category	Price	State
310056	17/02/2021	Service C	Complete	R$ 0	BA
310056	17/02/2021	Service C-1	Basic	R$ 0	BA
310056	17/02/2021	Service H	Complete	R$ 0	BA
310056	17/02/2021	Service H-1	Simple	R$ 3.984	BA
310056	17/02/2021	Service H-2	Popular	R$ 5.823	BA
310056	17/02/2021	Service L	Complete	R$ 0	BA
310056	17/02/2021	Service L-1	Popular	R$ 0	BA
310056	17/02/2021	Service L-2	Basic	R$ 0	BA
310056	17/02/2021	Service P	Complete	R$ 11.176	BA
310056	17/02/2021	Service P-1	Basic	R$ 10.278	BA
310056	17/02/2021	Service T	Complete	R$ 5.932	BA
310056	17/02/2021	Service T-1	Basic	R$ 5.920	BA
310056	17/02/2021	Service T-2	Popular	R$ 5.255	BA
310056	17/02/2021	Service T-3	Simple	R$ 0	BA

What I need to do, is to create a graphic (or a table) and show only the Service that have the lowest average price (ignoring the zeroes) per State, and apply in this view filters based on the month or the Service Category. Actually I could calculate the average per product and State using a simple DAX:

Average Price = CALCULATE(AVERAGE(FACT_QUOTES[Price]),FACT_QUOTES[Price]>0)

In this sample, it actually shows the average for all products per state "AC" in a table, without any filtering:

Sample Average Prices

What I need is to show only "Service L-1" in this scenario. In the same sample, if you filter only the "Complete" in a "Service Category" filter, I need to show only the line with "Service S" and so on for the other states.

I tried using TOPN on view filtering or inside a DAX Formula, RANKX also but it didn't work for me, maybe I'm missing something, so any help is appreciated. Searched for similar posts here but haven't found a similar situation. I don't know how to include a pbix or xlsx file here to include my sample files.

Thanks for your help!!

[MichelDias · Accepted solution]

OK, I found the solution and it was easier than I thought, what I did was to create a DAX function to reach only the lowest average using the SUMMARIZE function over the Service context:

IsMinAverage =

VAR MinorPrice = MINX(

SUMMARIZE(ALL(FACT_QUOTES[Service]),

FACT_QUOTES[Service]),

[Average Price]

)

RETURN IF([Average Price]=MinorPrice,1,0)

Once I used this, I could include a filter in my result table showing only rows where "IsMinAverage" = 1. Also, where there are no values in the range I needed to include an additional filter for "Average Price" > 0.

[Syk]

Try the TOPN filter on the visual and choose 'Bottom' 1 based on the Average Price

[MichelDias]

Thanks @Syk for the quick response, I already tried this but didn't work, when applying the TOPN filter in the visual, it only shows the same product for all States, no matter if there are other lower prices it gets only the one with the lowest average between all the filtered products.

[Syk]

So basically you need the cheapest service per state. (cheapest based on avg price)?

[MichelDias]

Exactly!