Filter customer based on percent order types

[Anonymous]

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:

[lbendlin]

you cannot color the row headers.  But you can color the values with conditional formatting based on a measure.

[Anonymous]

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.

[lbendlin]

Create a measure that calculated this percentage and use it as a filter for each visual.

[Anonymous]

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
    )

[lbendlin]

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

[Anonymous]

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

[lbendlin]

[Anonymous]

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 😞 😞 😞

[lbendlin]

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.

[Anonymous]

I have a column "has star" based on the orders; can this column be used to calculate that percentage?

[lbendlin]

 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.

[Anonymous]

well, as new orders come, the % will change...

[lbendlin]

If you use import mode and daily-ish refresh then a calculated column would still work.

[Anonymous]

Yes, great! Can you help me with this?

[lbendlin]

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)

[Anonymous]

Great, I have the 2 columns now; how to filter the customers by the percentage?

[lbendlin]

Add the column to a slicer, or better, to the "filters on all pages"

[Anonymous]

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

[lbendlin]

The flag is applied per customer.  Please check my code again.

[Anonymous]

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: