Forum Discussion
Add Row Showing % of Current Column
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.
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.
Thanks you very much for this. The formatting now works, of course, however I am getting 100% for all figures. I'll take a look when I get a chance and see if I can work out what's happening. It's likely something I've done.
thanks again
And yes, it was me that made a mistake, used the wrong version of Month, now works.
Thank you!