Forum Discussion
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
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!
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
2 Replies
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