Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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:
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:
Try the TOPN filter on the visual and choose 'Bottom' 1 based on the Average Price
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.
So basically you need the cheapest service per state. (cheapest based on avg price)?
Exactly!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |