Grand Total is not correct

[aslan]

Grand total must be 73,85 but it shows 73,78. Why is it? Can you please help?

[MarcelBeug]

The grand total of percentages will be the weighted average.

Example:

6,962 of 10,240 = 67.99%

7,971 of 10,000 = 79,71%

Grand total:

6,962+7,971 = 14,933

10,240 + 10,000 = 20,240

14,933 of 20,240 = 73.78%

[aslan]

It makes sense. I understood. Well do you have any suggestions do get 73.85 value? 

Also how did you find 10,240  and 10,000 values?

[MarcelBeug]

Second question first; I started with 6,799/10,000 and 7,971/10,000 and then started muliplying the first 2 values with a factor (trial and error). Factor 1.024 gave the outcome I was looking for.

First question: don't know why you wouldn't want to do that, but anyhow you can calculate the percentages as a calculated column and then include the average values in your report.

[aslan]

I didn't understand your solution. It looks correct but i don't know how use it.

Calculated column may work but I get value of 67.99 and 79.71 by (MeasureA-MeasureB)/MeasureA formula. So i don't know how can make a calculated column. MeasureA =SUM(Column1NUMBER) and MeasureB = DISTINCTCOUNT(Column2STRING) ( Also both of those columns are coming from different tables)

BTW; I use  Tabular as source and work Live on PowerBI.

[v-ljerr-msft]

Hi @aslan,

---

Calculated column may work but I get value of 67.99 and 79.71 by (MeasureA-MeasureB)/MeasureA formula.

---

Instead of using (MeasureA-MeasureB)/MeasureA, could you try the formula below to see if it works to get the right Grand Total you need?

Percentage = SUMX ( 'TableName', ( [MeasureA] - [MeasureB] ) / [MeasureA] )

Note: The 'TableName' is the name of your table whose column you're showing on the Table/Matrix with the Percentage measure.

Regards

[aslan]

Hi @v-ljerr-msft

It doesn't work. I am getting very small value in PowerBI.

[MarcelBeug]

Let me get this straight: 73,78% is the correct answer, but you are seeking for an incorrect answer.

That's why your measure results in 73.78%.

In order to get your incorrect answer of 73.85%, you need a calculated column with the percentages and then take the average of those percentages (excluding zeroes),

I know how to do the manipulation in Power Query to get the answer you are looking for, but not in DAX.

My advice would be to accept 73.78% as the correct answer.

[aslan]

Hi @MarcelBeug 73.78% is correct but client want to see 73.85% by calculating average. But couldn't create calculated column and because of that can't calculate average.

[MarcelBeug]

This is what I did in Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value1", Int64.Type}, {"Value2", Int64.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type", "Inserted Division", each [Value1] / [Value2], type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Division",{{"Inserted Division", Percentage.Type}})
in
    #"Changed Type1"

[aslan]

@MarcelBeugthere can be a way to use same logic with dax also but i am not well with dax because of that couldn't convert it to dax. But anyway thanks for your all help.

[aslan · Accepted solution]

This solved my issue

https://www.mrexcel.com/forum/power-bi/625617-grand-total-average-sum-sales-per-month.html