Forum Discussion
Add Row Showing % of Current Column
I'm writing a visualization that shows Months, Region and Spend. I need a row underneath each month that shows the % of spend against the total.
When I add a New Visual Calculation it adds this into a column, and for each month, so doubling the number of columns.
How can I do this so it adds underneath? Like the sample below:
to look like this:
In excel it's a simple thing to do, Power BI has me stumped. It's likely a simple thing and I'm not seeing how.
I can upload sample data and power bi files if needed (and someone can tell me how)
many thanks in advance
Barry
Hi,
Your 'Sum of Total' is aggregated by Power BI automatically. You will need to make a few Explicit Measures by creating 'New Measures', in your case they would be
Total = SUM('Vlookup - Exact'[Total])
Total Spend =
CALCULATE (
[Total],
ALL ( 'Vlookup - Exact'[Region] ), ALL ( 'Vlookup - Exact'[Month] )
)% of Spend Grand Total =
IF (HASONEVALUE( 'Vlookup - Exact'[Region] ),
BLANK(),
DIVIDE ( [Total], [Total Spend] )
)For the rest of steps, use 'Total' and '% of Spend Grand Total' Measures for your 'Values' in Matrix visual.
5 Replies
It's possible with some formatting techniques,
First update your '% of Spend' Measure to
_% of Spend = IF(HASONEVALUE(RegionTable[Region]), BLANK(), [% of Spend] )add your 'Total' and '_% of Spend' Measure both on Values, enable 'Switch values in row groups rather than columns' in Values formatting.
The Matrix will display,
Use 'None' for Style and 'Tabular' for layout, blank out your Measure names in Values by using space button after names selected.
You will have similar layout like Excel as below.
Hi there, and thank you for your reply.
I'm lost with the first part of your suggestion, update my measure. Currently I am using a matrix table and literally using 3 fields from my data in it, so no measure was created. Is that was I should be doing, and where in the measure would I add your formula?
Hi,
Your 'Sum of Total' is aggregated by Power BI automatically. You will need to make a few Explicit Measures by creating 'New Measures', in your case they would be
Total = SUM('Vlookup - Exact'[Total])
Total Spend =
CALCULATE (
[Total],
ALL ( 'Vlookup - Exact'[Region] ), ALL ( 'Vlookup - Exact'[Month] )
)% of Spend Grand Total =
IF (HASONEVALUE( 'Vlookup - Exact'[Region] ),
BLANK(),
DIVIDE ( [Total], [Total Spend] )
)For the rest of steps, use 'Total' and '% of Spend Grand Total' Measures for your 'Values' in Matrix visual.
