cancel
Showing results for
Did you mean:
Helper III

## Dynamic Axis by selected percentage range

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

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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

Many thanks! It's exactly what I want

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.