Aggregation of row level measure

[knowledgegarage]

Hi,

I have an interesting issue I cannot resolve in Power BI.

I have a measure that is a row level calculation; as determined (for each unique sub-type of product, for each product)

This computes a value for each row and in theory it should then sum up to 100%. It doesn't for reasons which are not relevant. However I would like to normalise this by dividing the measure e.g. 1.6% by the sum of the column e.g. 97%.

Unfortunately PowerBI is not as intuitive as to how this can be done. I cannot appear to get it to work despite using allexcept and other methods.

Basically I need to create a column that is a fixed value i.e. the 97%. I thought about just dividng by 97% but each region may have a different subtotal.

The measure Sales Value (%) is the row level calculation. This is the table I have today

Region	Product Type	Product Sub-type	Product	Sales (%)	Value Normaliser	Sales Value (%)
EMEA	Primary	Sus-37	129	1.58%	1	1.58%
EMEA	Primary	Kno-45	698	3.07%	1	3.07%
EMEA	Primary	Eff-22	659	1.52%	1	1.52%
EMEA	Primary	Kno-45	698	1.66%	0.6	0.99%
EMEA	Secondary	Saf-72	730	1.66%	0.4	0.66%
EMEA	Primary	Qua-66	745	1.71%	0.6	1.03%
EMEA	Secondary	Sus-37	129	1.71%	0.4	0.69%
EMEA	Secondary	Hea-99	152	1.75%	0.2	0.35%

I require a Table to look like this:

Normalised Sales Value (%) would be the Sales Value (%) divided by the Sum of Sales Value (%)

Region	Product Type	Product Sub-type	Product	Sales (%)	Value Normaliser	Sales Value (%)	Sum of Sales Value (%)	Normalised Sales Value (%)
EMEA	Primary	Sus-37	129	1.58%	1	1.58%	97%	1.63%
EMEA	Primary	Kno-45	698	3.07%	1	3.07%	97%	3.16%
EMEA	Primary	Eff-22	659	1.52%	1	1.52%	97%	1.57%
EMEA	Primary	Kno-45	698	1.66%	0.6	0.99%	97%	1.02%
EMEA	Secondary	Saf-72	730	1.66%	0.4	0.66%	97%	0.68%
EMEA	Primary	Qua-66	745	1.71%	0.6	1.03%	97%	1.06%
EMEA	Secondary	Sus-37	129	1.71%	0.4	0.69%	97%	0.71%
EMEA	Secondary	Hea-99	152	1.75%	0.2	0.35%	97%	0.36%

Additional details:

My 'Sales Value (%) measure is as follows:

Sales Value (%) = SUMX(SUMMARIZE('Sales','Sales'[Product Sub-type],'Sales'[Product],"Subtotal",SUM('Sales'[Sales (%)])*SUM('Product'[Value Normaliser])),[Subtotal])

Region is pulling from another table 'Master', Value Normaliser from 'Product' and everything else from 'Sales'.

Thank you in advance.

[knowledgegarage]

Ok, so I now have a new problem due to requirements.

The end user wants to see the split by Product Type.

When I use the following formula each product type split on the chart adds up to 100%; thus it is a percentage of the total data for each product type instead of overall.

Sales Value (%) Total = CALCULATE([Sales Value (%)], ALLSELECTED())

Any ideas?

[lbendlin]

You can use SELECTEDVALUE() to figure out which ProductType you are currently looking at in each cell of your visual.  But that won't work for the totals. There you need to collect all possible product types, and then compute the result across them.  Incidentally that approach will also work for individual cells, ovbiating the need for SELECTEDVALUE.  The saying goes "Think like the Grand Total".

[knowledgegarage]

Noted.

I have created two new fields:
Sales Value (%) Total = CALCULATE([Sales Value (%)], ALLSELECTED())

Sales Value Nm = 'Sales'[Sales Value (%)] / 'Sales'[Sales Value (%) Total]

Sales Value (%) gives me the fixed total, as per filter

Sales Value Nm gives me the normalised row level calculation

Thank you

[lbendlin · Accepted solution]

you're on the right path with SUMX.  But you need to do that for both the "row" and for the entire filter context (ALLSELECTED)

By the way, "row" is an ambiguous term.  You DO have rows in your source tables. Measures do NOT apply to them (calculated columns do).  You MAY have "rows" in some visuals, but try to think of these not as rows but as collections of filters (ie filter context). Measures DO apply to these.