Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aslan
Helper I
Helper I

Grand Total is not correct

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

 

1 ACCEPTED SOLUTION
11 REPLIES 11
MarcelBeug
Community Champion
Community Champion

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%

Specializing in Power Query Formula Language (M)

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
Community Champion
Community Champion

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.

 

Unweighted average percentage.png

Specializing in Power Query Formula Language (M)

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.

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?Smiley Happy

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

Hi @v-ljerr-msft

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

MarcelBeug
Community Champion
Community Champion

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.

 

 

Specializing in Power Query Formula Language (M)

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
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.