Forum Discussion
Custom horizontal barchart
Hi,
For esthetic purpose, I like to create a horizontal stack barchart with values, that looks like a 100% stacked barchart.
So i need to calculate the most expensive category, substracting the result to other categories to fill the gap in the barchart.
Starting with this kind of dataset.
| Date | Category | Cost |
| 2025-01-01 | Compute | 100 |
| 2025-01-01 | Storage | 50 |
| 2025-01-01 | Network | 10 |
| 2025-02-01 | Compute | 150 |
| 2025-02-01 | Storage | 60 |
| 2025-02-01 | Network | 20 |
| 2025-03-01 | Compute | 200 |
| 2025-03-01 | Storage | 70 |
| 2025-03-01 | Network | 30 |
Got a pivot table like this, I'd like to calculate the gap column through a dax measure.
| Category | [TotalCost] | GAP to be calculated |
| Compute | 450 | 450-450 = 0 |
| Storage | 180 | 450-180 = 270 |
| Network | 60 | 450 - 60 = 390 |
So I can built a horizontal barchart that looks like a 100%
I'm struggling to calculate this gap column, any clue ?
I thank you in advance folks.
Jay20024 Create a new measure to calculate the total cost for each category.
Create another measure to calculate the GAP.TotalCost = SUM('YourTable'[Cost])
MaxCost = CALCULATE(MAXX(ALL('YourTable'[Category]), [TotalCost]))
GAP = [MaxCost] - [TotalCost]
Add a stacked bar chart to your report.
Drag the Category field to the Axis.
Drag the TotalCost measure to the Values.
Drag the GAP measure to the Values.
5 Replies
Hello Jay20024
This measure should return what you need
GAP =VAR MaxCost = CALCULATE(MAX('Table'[TotalCost]), ALL('Table'))RETURNMaxCost - SUM('Table'[TotalCost])If it is not what you are trying to achieve, let me know 🙂Another way to do it, Many thanks for this feedback
Jay20024 Create a new measure to calculate the total cost for each category.
Create another measure to calculate the GAP.TotalCost = SUM('YourTable'[Cost])
MaxCost = CALCULATE(MAXX(ALL('YourTable'[Category]), [TotalCost]))
GAP = [MaxCost] - [TotalCost]
Add a stacked bar chart to your report.
Drag the Category field to the Axis.
Drag the TotalCost measure to the Values.
Drag the GAP measure to the Values.Works like a charm ! Many thanks bhanu_gautam ! You rock, and you allowed me to improve my understanding of Dax !
Also a nice trick to improve UI
