cancel
Showing results 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

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

1 ACCEPTED SOLUTION
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] )`

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

2 REPLIES 2
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] )`

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

Helper III

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.

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors