Power BI Manipulating/Fixing Totals

Recently, lots of people have been talking about fixing the totals in Power BI.

We need to understand how Power BI calculates the total so it will help us manipulate it.

Profit Margin = DIVIDE([Profit],[Revenue],0)        

For instance, if we use the Dax above to get the profit margin, the total would be calculated in this manner:

(Total Profit) / (Total Revenue)

Hence, profit margin will be for the totals of Profit and the totals of Revenue. If we want to get Average Profit Margin in the total, you can use the following Dax:

Profit Margin Avg =
AVERAGEX(
? ? SUMMARIZE(
? ? ? ? Sales,
? ? ? ? 'Product Category'[Category],
? ? ? ? "PM",[Profit Margin]
? ? ),
? ? [PM]
)        

What summarize does is it creates a table with one column of category and a second column for profit margin (named PM) for each category. Then use this AverageX() to get the average of those Margin values. One thing is to be noted, the average for each category will be the same, only total value will be affected as shown in the matrix visual

Another problem we can solve with this technique is to eliminate duplicated entries, such as those shown from the data:

Revenue = SUMX(Sales,Sales[Unit Price]*Sales[Quantity])


Revenue Corrected =
SUMX(
? ? SUMMARIZE(
? ? ? ? Sales,
? ? ? ? Sales[ProductKey],
? ? ? ? "Rev",AVERAGE('Sales'[Unit Price])*AVERAGE(Sales[Quantity])
? ? ),
? ? [Rev]
? ? )         

You may use power query group by to get unique values, but if you have millions of rows with only few duplicate entries, group by will might not be the most effecient approch.

The result will be :

So what this summarize function is doing here is getting the average for each product key, and since it was duplicate, the average will give a value for one product key. Then SumX() will sum this up for the catergories in rows of the matrix visual.