Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I really need help with this...
I need to have a slicer in a viz, that can be used to filter only the customers that have more than 2% from their orders for a specific item (ex.: star). I'd like to filter and see in the viz only customers that have more than 40% or their orders for star.
Here is example data:
you cannot color the row headers. But you can color the values with conditional formatting based on a measure.
I don't want to color them, I need to filter so only the customers that have more than 40% of their orders for star remain in my viz;
I have bar charts and pie charts per region/country/ year and I want to show only the customers I care for in those charts.
Create a measure that calculated this percentage and use it as a filter for each visual.
Well, I've been trying for a few days, and tested hundreds of DAXes, also with the help of ChatGPT, but even if I get to a result, once I use the slicer - it removes all non-star orders; that is not my goal; I need to keep all orders but only customers that have more than 2% of orders containing "star".
Ex: (it doesn't give me the wanted result, but just to show you)
StarOrderPercentage = DIVIDE( CALCULATE( COUNTROWS(FILTER('table', 'table'[Type] = "star")), ALLEXCEPT('table', 'table'[Customer]) ), CALCULATE( COUNTROWS('table'), ALLEXCEPT('table', 'table'[Customer]) ), 0 )
ALLEXCEPT is too blunt of a hammer. Use subtler tools like REMOVEFILTERS.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Ok, thanks for the advice.
Here is an example table (imagine I have real data with country, date, etc.):
Order | Customer | Type |
1 | a | star |
2 | b | star |
3 | c | star |
4 | d | circle |
5 | a | triangle |
6 | b | triangle |
7 | c | circle |
8 | a | star |
9 | b | circle |
10 | a | triangle |
11 | b | star |
12 | c | triangle |
13 | d | triangle |
14 | a | star |
15 | b | triangle |
16 | c | circle |
17 | d | circle |
18 | a | star |
19 | b | circle |
20 | a | star |
Customer a has 7 orders, 5 for star, so 71%;
customer b has 6 orders, 2 for start so 33%;
customer c has 4 orders, 1 for star, so 25%;
customer d has 3 orders, 0 for start, so 0%;
So I want my slicer to let me filter and have in the viz customer a with his 7 orders and b with his 6 orders, because they have over 30% of their orders for star.
I'll try to be more visually explanatory.
Let's say circle is dark blue, star is green, triangle is grey:
I'd like to have a slicer in my viz, that I can use to filter only customers that have more than 30% of their orders for star, but see all their orders (not very well visible, but the others are greyedout):
- I'll see only the customers with above 30% star orders but all treir orders included:
It is challenging, at least for me, it is....
Thank you for the effort!
But this can't be used in a slicer...I need to be able to filter or not filter, that's why I need a solution for a slicer, not pbix filter 😞 😞 😞
You cannot have slicers based on measures. If your star percentage never changes (is not impacted by users interacting with the report) you can create a calculated column instead which you can use in a slicer.
I have a column "has star" based on the orders; can this column be used to calculate that percentage?
If your star percentage never changes (is not impacted by users interacting with the report) you can create a calculated column instead which you can use in a slicer.
well, as new orders come, the % will change...
If you use import mode and daily-ish refresh then a calculated column would still work.
Yes, great! Can you help me with this?
Great, I have the 2 columns now; how to filter the customers by the percentage?
Add the column to a slicer, or better, to the "filters on all pages"
It removes the orders with star if I choose 0 or the non-starts if I filter by 1.
I need to filter the customers, not the orders
The flag is applied per customer. Please check my code again.
yes, I recreated it in my report and just changed the percentage:
"return if(d>.02,1,0)";
however, the slicer options are "1" and "0":
When I choose "0" - yes, it removes the customers with less %, but also the % changes of those who remain:
When I choose "1", it just removes all data for non-star:
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |