Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
DiKar
Helper I
Helper I

Filter customer based on percent order types

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:

DiKar_0-1700569505019.png

 

23 REPLIES 23
lbendlin
Super User
Super User

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.):

OrderCustomerType
1astar
2bstar
3cstar
4dcircle
5atriangle
6btriangle
7ccircle
8astar
9bcircle
10atriangle
11bstar
12ctriangle
13dtriangle
14astar
15btriangle
16ccircle
17dcircle
18astar
19bcircle
20astar

 

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:

DiKar_1-1700744105548.png

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):

DiKar_3-1700744390487.png

- I'll see only the customers with above 30% star orders but all treir orders included:

DiKar_2-1700744347493.png

It is challenging, at least for me, it is....

 

 

lbendlin_0-1700751200113.png

 

 

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?

Over 30% Star Col = 
var c =[Customer]
var f = filter(ALL('Table'),'Table'[Customer]=c)
var d = 0+divide(countrows(FILTER(f,[Type]="star")),countrows(f),0)
return if(d>.3,1,0)

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":

DiKar_0-1701333587290.png

When I choose "0" - yes, it removes the customers with less %, but also the % changes of those who remain:

DiKar_3-1701333939912.png

 

When I choose "1", it just removes all data for non-star:

DiKar_1-1701333684467.png

 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.