Value in a matrix as a percentage of another row value

[Nishantp]

I have a typical profit and loss table (very simplified and dummy data version) that looks like this:

Month	Type	Actual	Budget	Variance
January	Internet sales	500000	525000	-25000
January	Store sales	650000	600000	50000
January	Sales all-up	1150000	1125000	25000
January	Product cost	800000	785000	15000
January	Web fees	80000	60000	20000
January	store rent	120000	120000	0
January	Income	150000	160000	-10000
February	Internet sales	525000	515000	10000
February	Store sales	625000	600000	25000
February	Sales all-up	1150000	1115000	35000
February	Product cost	795000	785000	10000
February	Web fees	75000	60000	15000
February	store rent	120000	120000	0
February	Income	160000	150000	10000

I want to show this in PowerBI as a matrix that would look something like this:

Type	Actual	% of total sales	Budget	% of total sales_	Variance
Internet sales	1025000		1040000		-15000
Store sales	1275000		1200000		75000
Product cost	1595000		1570000		25000
Web fees	155000		120000		35000
store rent	240000		240000		0
Income	310000		310000		0

By default the data is summation of all the months e.g. internet sales is internet sales for Jan + Feb. But there will be a slicer that allows the user to filter on any month or see multiple months at the same time.

The question: How do I show the column % of total sales? What I need is for PowerBI to take the value of the actual for internet sales and divide that by total sales for that time period and do that for all the rows.

[parry2k]

@Nishantp you can surely do it but you don't want to do the SUM measure again and again. Also recycle the measure, that's why I created a SUM measure and used at both the place. End of the day it doesn't matter but as a best practice it is good to recycle/reuse, and it is more scalable and manageable.

Better to avoid shortcuts and makes the habits of creating scalable solution following best practices.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Ashish_Mathur]

Hi,

Not clear of what you want.  In the second table, you have shown % in the title but are adding absolute values under that column.  Why? 

[Nishantp]

Hi Ashish,

In the second table, for the % of total sales..I need to show values. I am not showing anything right now. The idea is that for every type (internet sales, product cost, rent, etc.) we need to show the number and next to it what is is in terms of % of total sales. Showing the actual number for internet sales is easy as it is coming from the table but % of total sales should be sum (internet sales) / sum (sales all-up) and then for rent it woudl be sum (rent) / sum (sales all-up)...so on.

I believe I will have to create a measure for % of total sales and have it as a value in the matrix. I have seen some folks do that but I am not able to create one measure that will calculate correct values for every row.

Hope I am making sense.

[parry2k]

@Nishantp here are the measures for actual and you can apply for the rest

Actual = SUM ( Table[Actual] )

% of Actual Total Sales = DIVIDE ( [Actual], CALCULATE ( [Actual], ALLSELECTED( Table[Type] ) ) )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Nishantp · Accepted solution]

Hi Parry2k,

I was able to make it work by creating only one measure. the formula I used was:

% of Actual Total Sales = DIVIDE ( SUM ( table[Actual] ), CALCULATE ( SUM ( Table[Actual]),Table[type]="Sales all-up"))