Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Waterfall chart between two measures by dimension

Hello,

 

I am trying to create a waterfall chart that shows the gap between two calculated measures by a dimension. However waterfall should show the only top "offenders" in terms of gap between the two measure. Much like the photo below, in the example below the top two customers are shown and the rest are aggregated under Other. I have done this before in other BI tools however I cant seem to find a straight forward way to do it in PBI.

 

Expected Output:

I have the dataset and example calculation here

Measure Formulas:

MeasureA ->P+L
MeasureB ->P+L+C

 

Measure Calculation:

MeasureA
CustomerA350
CustomerB450
CustomerC400
CustomerD450
CustomerE250
 1900

 

MeasureB
CustomerA200
CustomerB350
CustomerC200
CustomerD350
CustomerE150
 1250

 

 

Dataset:

YearCustomerBlockValue
2023CustomerAP100
2023CustomerAL250
2023CustomerAC-150
2023CustomerBP200
2023CustomerBL250
2023CustomerBC-100
2023CustomerCP150
2023CustomerCL250
2023CustomerCC-200
2023CustomerDP150
2023CustomerDL300
2023CustomerDC-100
2023CustomerEP100
2023CustomerEL150
2023CustomerEC-100

 

I am familiar with the general method that involves creating a disconnected table + using a switch measure to get the gap however you have to hard code the dimension values in the disconnected table. 

 

Any help? Thank you!