Forum Discussion
Seeking Optimal Approach: Creating Visuals for Clustered Column Chart
I'm facing a challenge in creating a visual representation of the attached clustered column chart. Currently, I've managed to address this by generating multiple sheets in Excel, which I subsequently import into Power BI Desktop. These sheets calculate reductions of 10%, 15%, and 25%. I introduce an additional column and compute the following: -10% = Loan / Value * 0.9. Subsequently, I utilize this LTV value to formulate a new column for the LTV Interval: Under 40%, 40%-60%, and so on.
Furthermore, I have a helper table linked to both sheets, wherein I've established categories like Under 40%, 40%-60%, etc.
Within the clustered column visual, I employ the Under 40% category from the helper table on the X-axis, and the Loan values from the various sheets on the Y-axis.
Original sheet
| Loan | Value | LTV | LTV Interval |
| 11 | 20 | 0,55 | 40%-60% |
| 5,5 | 15 | 0,37 | Under 40% |
| 10 | 10 | 1 | Over 100% |
Calculated sheet
| Loan | Value - 10% | LTV | LTV Interval |
| 11 | 20*0,9=18 | 0,61 | 60%-70% |
| 5,5 | 15*0,9=13,5 | 0,41 | 40%-60% |
| 10 | 10*0,9=9 | 1,11 | Over 100% |
Value -15% etc generates the following visual:
This workflow has proven to be intricate and resource-intensive, leading to an accumulation of extraneous data and Excel sheets. Could this procedure be streamlined through the use of measures instead? It's worth noting that I have attempted this approach, but encountered a challenge where the Loan value exclusively referred to categories like "Under 40%", resulting in unintended outcomes when applying a subtraction of 10%.
For the sake of precision, it's essential that the total Loan remains consistent across all three scenarios. These scenarios should only shift within the different intervals as the Value parameter changes. Consequently, summing up the columns for the diverse scenarios should yield a consistent total sum.
Is there a more efficient approach to resolve this matter, while preserving accuracy and optimizing resource utilization?
9 Replies
Is there anything stopping you having all the columns in the same table. i.e. you have one table with the following columns:
Loan
Value
LTV
LTV Interval
Value (10% reduction)
LTV (10% reduction)
LTV Interval (10% reduction)
Value (15% reduction)
LTV (15% reduction)
LTV Interval (15% reduction)
Value (25% reduction)
LTV (25% reduction)
LTV Interval (25% reduction)
At the very least this would keep everything on one Excel sheet. Even better, you could then have all the calculated columns set up as steps in the Power Query instead, so you're not calculating anything extra in Excel.
Hi
You're right and I have tried this. The problem occurs when I then want to present this as a visual like the clustered column chart which is based on the loan value. I have thought about making 3 equal loan value columns which are somehow linked to the different LTV Intervall columns, but I still dont have it completely figured out. I also tried so make som measures but then problem was that the measure only calculated on the total and then I got one answer for the whole dataset... So Im still searching for the best solution.
Maybe restructure your data so it's more like this:
Loan Value LTV LTV Interval Reduction Type 11 20 0.55 40%-60% None 11 20 * 0.9 Calculated Calculated 10% 11 20*0.85 Calculated Calculated 15% 11 20*0.75 Calculated Calculated 25% 5.5 15 0.37 20%-40% None 5.5 15*0.9 Calculated Calculated 10% 5.5 15*0.85 Calculated Calculated 15% 5.5 15*0.75 Calculated Calculated 20% You could do this in Power Query by puling the same source multiple times & then appending them all together.
Im not sure that I follow what you are thinking... I need to calculated all the values with the same percentage, so that I can see what happens if all the values decreases with 10-15-25%. Hopefully you understand what I mean:) If you see my column chart you can see that I have 4 different bars with the original data and then the stressed values.
Yes, so if your original table has 100 rows, then the table I'm proposing has 400 rows:
100 rows for no reduction
100 rows for 10%
100 rows for 15%
100 rows for 25%
All stacked vertically in one table
Then the values in your chart can just be SUM([Value]), legend is [Reduction Type] and x-axis is [LTV Interval].
