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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
OSS
Helper III
Helper III

Incorrect Grand total value

Dear all

I have a problem with the grand total calculation. 

 

I have uploaded my pbix. I think this way is the easiest way to explain and understand my problem. I need to receive a correct grand total for 'POS amount' column.

 

https://we.tl/t-9syB81uTZs

 

Please help me with this issue. 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

First question, in 'Cards Mapping' it looks like there is a duplicate value.  Could this be causing some problem?

  • [Full Acc. Def.] 87017AZNBE0700000001 is in there twice with a POS% of 0.253560889064541

 

For the total calc I would do the following:

 

First write a couple measures to make the last setp easier

TotalCardExp = sum( 'Cards YTD'[Total Card Exp] )
POS%measure = sum( 'Cards Mapping'[POS%] )

Then you can write a measure that will go through the 'FULL ACC' table and do your calc.

POSAmount = SUMX ( 'FULL ACC', [TotalCardExp] * [POS%measure] )

PowerBI answer.jpg

 

The problem you are seeing in your report is that Grand Totals are not the sum of all the numbers above, they are the measure calculated with no filters so it is giving you [Total Card Exp] * [POS%] -6,556,122.88 * 17.79 = -116,631,611.78

 

 

 

 

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

First question, in 'Cards Mapping' it looks like there is a duplicate value.  Could this be causing some problem?

  • [Full Acc. Def.] 87017AZNBE0700000001 is in there twice with a POS% of 0.253560889064541

 

For the total calc I would do the following:

 

First write a couple measures to make the last setp easier

TotalCardExp = sum( 'Cards YTD'[Total Card Exp] )
POS%measure = sum( 'Cards Mapping'[POS%] )

Then you can write a measure that will go through the 'FULL ACC' table and do your calc.

POSAmount = SUMX ( 'FULL ACC', [TotalCardExp] * [POS%measure] )

PowerBI answer.jpg

 

The problem you are seeing in your report is that Grand Totals are not the sum of all the numbers above, they are the measure calculated with no filters so it is giving you [Total Card Exp] * [POS%] -6,556,122.88 * 17.79 = -116,631,611.78

 

 

 

 

Thank you very much.

 

And another big thanks for dublicate value. It was a problem and I couldn't find the source 🙂

 

You solved my two problems with one post 🙂 Thank you again.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors