Dynamic Axis by selected percentage range

[Anonymous]

Hi PB Experts,

The problem is as below:

I want to display Customer sales by % range, the Output line chart will be controled by the Slicer

The PBIX file is prepared PBIX Download 

If I select the range 0%-20% (Customer14/ Customer11/ Customer15) will be shown

If I select the range 21%-40% (Customer12/ Customer13/ Customer08) will be shown

Hope that someone can help, many thanks!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I just updated your sample pbix file as below, you can get the updated file from this link:

1. Change the formula of calculated table "Axis" as below:

Axis = GENERATESERIES(0, 100, 1)

2. Create a new measure with below formula

Measure = 
VAR _countC =
    CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[CustomerID] ), ALL ( 'Sheet1' ) )
VAR _percent =
    ROUND ( DIVIDE ( 1, _countC ) * [Rank], 2 ) * 100
RETURN
    IF (
        _percent >= MIN ( 'Axis'[Axis] )
            && _percent <= MAX ( 'Axis'[Axis] ),
        SUM ( Sheet1[Sales] ),
        BLANK ()
    )

Best Regards

Rena

[Anonymous]

Many thanks! It's exactly what I want