Hi All
I have been tearing my hair trying to get to a required graphical visual (stacked column bar chart) for my data
I've trawled through the community, Powerbi sites etc but cannot seem to get any suggestion to work
The main issue is getting (converting/using) a measure value as a legend or axis in a chart
I have 4 tables
DataTable
| Company | LoB | Type | Year | Amt |
| A | A1 | Revenue | First | 100 |
| A | A1 | Expense | First | 50 |
| A | A2 | Revenue | First | 125 |
| A | A2 | Expense | First | 50 |
| B | A1 | Revenue | First | 90 |
| B | A1 | Expense | First | 50 |
| B | A3 | Revenue | First | 25 |
| B | A3 | Expense | First | 50 |
| C | A1 | Revenue | First | 1000 |
| C | A1 | Expense | First | 435 |
| C | A2 | Revenue | First | 900 |
| C | A2 | Expense | First | 450 |
| C | A5 | Revenue | First | 4900 |
| C | A5 | Expense | First | 2450 |
| D | A2 | Revenue | First | 1000 |
| D | A2 | Expense | First | 435 |
| D | A3 | Revenue | First | 900 |
| D | A3 | Expense | First | 450 |
| D | A4 | Revenue | First | 1900 |
| D | A4 | Expense | First | 1450 |
| A | A1 | Revenue | Second | 110 |
| A | A1 | Expense | Second | 55 |
| A | A2 | Revenue | Second | 137.5 |
| A | A2 | Expense | Second | 55 |
| B | A1 | Revenue | Second | 99 |
| B | A1 | Expense | Second | 55 |
| B | A3 | Revenue | Second | 27.5 |
| B | A3 | Expense | Second | 55 |
| C | A1 | Revenue | Second | 1100 |
| C | A1 | Expense | Second | 478.5 |
| C | A2 | Revenue | Second | 990 |
| C | A2 | Expense | Second | 495 |
| C | A5 | Revenue | Second | 5390 |
| C | A5 | Expense | Second | 2695 |
| D | A2 | Revenue | Second | 1100 |
| D | A2 | Expense | Second | 478.5 |
| D | A3 | Revenue | Second | 990 |
| D | A3 | Expense | Second | 495 |
| D | A4 | Revenue | Second | 2090 |
| D | A4 | Expense | Second | 1595 |
dimCountry
| Country | Company |
| Alpha | A |
| Alpha | B |
| Beta | C |
| Beta | D |
dimBusiness
| Business | LoB |
| Road | A1 |
| Road | A3 |
| Rail | A2 |
| Rail | A4 |
| Air | A5 |
PMGroup
| PM% Group | Min | Max |
| Top | 0.75 | 1000000 |
| Middle | 0.25 | 0.75 |
| Bottom | -1000000 | 0.25 |
DataTable related to dimCountry on company
DataTable related to dimBusiness on Lob
From my data model/set I can calculate using measures
Revenue = CALCULATE(SUM('DataTable'[Amt]), 'DataTable'[Type] IN { "Revenue" })
Expense = CALCULATE(SUM('DataTable'[Amt]), 'DataTable'[Type] IN { "Expense" })
Profit = [Revenue] - [Expense]
Profit Margin % = DIVIDE([Profit], [Expense],0)
Profit Margin Group =
IF(ISBLANK([Profit Margin %])
,BLANK()
,CALCULATE(VALUES(PMGroupTable[PM% Group]),filter(PMGroupTable,PMGroupTable[Min] <=[Profit Margin %] && PMGroupTable[Max]>=[Profit Margin %]))
)
Now this is great to view the data via a matrix table or table BUT now i have a requirement to produce a stacked column bar chart where the Profit Margin Group is the legend, the Year is the axis and Profit is the value
So my issue is gettingthe Profit Margin Group as the graph legend 😞
The grouping for the bar chart is based on the grouping assigned at the Country and Business level.
I have a sample pbix file of the above but due to business contraints do not have somewhere to store it (no dropbox etc)
Also have screenshots but also no way of entering to here 😞
Any help greatly appreciated
Cheers
Telstar
