topic Re: Make matrix visual to show only customers with sales that meet a threshold amount set by a slicer in DAX Commands and Tips

Make matrix visual to show only customers with sales that meet a threshold amount set by a slicer

leolapa_br — Thu, 15 Aug 2024 20:33:43 GMT

Say I have a simple model with a 'Sales' fact table with thousands of rows, and from such fact table I wrote the following measure:

Total sales by customer = SUMX( Sales, Sales[Price] * Sales[Quantity] )

And then I produced the matrix visual below sorted in descending order by total sales amount:

What I need is after setting up a single-value slicer (which I already did via New parameter → Numeric range), dynamically update the matrix so it only shows those customers whose total sales meet the threshold set by the slicer.

Below I graphically show the transition from the original matrix visual (left) to how it should look like (right):

Say I change the threshold amount to $30,000, now the matrix should dynamically change and look like the following:

The highlighting part should be no big of a deal as conditional formatting should take care of that part, but I anticipate challenges to attend the following requirements:

Remove the customers below the threshold (I guess some measure using TOPN might deal with that?).
Create an additional "Others" row that sums up all customers below the threshold (and as a bonus feature, dynamically display the total number of customers under such status).
Also display the overall total number of customers next to "Total".
And then add an additional row at the very bottom that shows the total number of customers that meet the threshold and the sum of total sales (and also highlight this row to visually indicate its purpose).

Any help is greatly appreciated. If I can get past the 1st requirement I would already be a happy camper...

Re: Make matrix visual to show only customers with sales that meet a threshold amount set by a slicer

jgeddes — Thu, 15 Aug 2024 20:50:47 GMT

You cannot get the exact result you are looking for in the totals of a matrix or table visual. You can get something similar using cards. I have attached a basic example file.
Hope this helps.

Re: Make matrix visual to show only customers with sales that meet a threshold amount set by a slicer

leolapa_br — Fri, 16 Aug 2024 13:04:49 GMT

Thanks for your efforts jgeddes, much appreciated!

This for sure will help me with requirements 3. and 4. provided some compromises and adaptation will be needed to the originally designed solution.

As for requirements 1. and 2. I came across this excelent video by SQL BI's Alberto Ferrari that takes care of them: Filter top 3 products with a row for others using DAX in Power BI - SQLBI.

Thanks!

Re: Make matrix visual to show only customers with sales that meet a threshold amount set by a slicer

Anonymous — Mon, 19 Aug 2024 01:50:17 GMT

Hi leolapa_br ,

Is your problem solved? If so, please mark jgeddes's answer as the solution. If not, you are welcome to continue asking questions.

Best Regards,
Wenbin Zhou