Segment based on a meausre and used as filter?

[Manudata]

 

Hello, I am unable to solve this (with other tools I know how to do it, but with PBI I don't see a way).

I have a list of invoices, year, customer name, sales and margin amounts.

I want to know the margin percentage of each client and then group them into segments (margin <30%, margin 30-60%, margin 60-100%)

Then represent the number of customers for each segment (in a pie chart for example) and you can click on the pie and the list with that segment is filtered

The main problem is:

- If I create measures to calculate customers by segment, that measure does not filter a list
- If I use calculated columns, they are not recalculated if I later use a filter on the screen

Any ideas?

pbix with sample data

https://drive.google.com/file/d/1QBuuQ6IvqbcPoLNxZThxl6SfmW_Xnzyi/view?usp=sharing

[Manudata]

any ideas?

[Anonymous]

Hi,

Thanks for the solution SamWiseOwl  offered, and i want to offer some more information for user to refer to.

hello @Manudata , you can refer to the following sample.

1.Create a range measure.

Range =
SWITCH (
    TRUE (),
    [% margin] >= 0
        && [% margin] < 0.25, "0-25%",
    [% margin] >= 0.25
        && [% margin] < 0.5, "25%-50%",
    [% margin] >= 0.5
        && [% margin] < 0.75, "50%-75%",
    "75%-100%"
)

2.Then create a range table.

3.Then create a measure.

Measure = COUNTROWS(FILTER(Tabla,[Range] in VALUES('Table'[Margin_range])))

4.Put the following field to the pie chart visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Manudata]

Hi, this solutions works only for donut chart, but it does not allow clicking on the donut to filter the list (expected because the measurements do not allow filtering other objects)

[SamWiseOwl]

Hi @Manudata 

You can create a table using Enter data and enter the definition of the bandings.

Then use this to group and count the items that fall into the different categories.

Create a measure that uses this in the pie chart.

Count values =

var big = SELECTEDVALUE(Bandings[Max])

var small = SELECTEDVALUE(Bandings[Min])

RETURN

 COUNTX(Filter(Tabla, [% margin]*100 <= big &&[% margin]*100 >= small),[customer])

[Manudata]

This solution not allow clicking on the donut to filter the list (expected because the measurements do not allow filtering other objects)