Stacked bar chart - values as part of the whole column (normalized)

[djones]

Hi all,

Thanks in advance for the help. I'm new to Power BI and could use some assistance. Example of data:

	column1	column2
category1	200hrs	2800hrs
category2	100hrs	3300hrs
category3	300hrs	4700hrs
		10800

I'm trying to show how often each category, column 1 occurred per 1000 hours of the sum of column 2. So 200/10800 = .018519 * 1000 = 18.518 hours per 1000 hours of the sum (from column 2)

category1	0.018519	18.51852
category2	0.009259	9.259259
category3	0.027778	27.77778

The 18.51, 9.25, 27.77 is what I need graphed in a stacked bar chart, by year, month, week. I need to be able to drill into each of these levels, so I need the aggregation to by dynamic. Right now all I can seem to do is have 200/2800 = .071 * 1000 = 71.42, which is not what I'm looking to do. If I need to provide any further information please let me know.

Thanks

[MFelix · Accepted solution]

Hi @djones ,

Add the following measure to your model:

Measure =
SUM ( Table1[column1] )
    / CALCULATE ( SUM ( Table1[column2] ); ALL ( Table1[Category] ) ) * 1000

Regards,

MFelix