Wrong sum total on my report PowerBi

[laurarajao]

Hello,

I'm new to Power BI and I'm having some problems with a table in my report. On my report, I have a table and I'm trying to display the totals of the different columns.

However, I notice that for one column (which is a measure and which I'm going to call: JTT 12 MOIS), the results in the table are correct but the total is wrong: for example, I get a total of 53,000,000 and if I do this calculation manually, the result is 1,500,000.

In analyzing my data, I thought that this problem might be due to the fact that the JTT 12 MOIS measure was the product of a numerical number multiplied by a percentage, and that POWER BI was having trouble handling this calculation in order to total up the column.

Here it is : 

JTT 12 MOIS =

[JTT 12 MOIS APAP] * [PDM 12 MOIS]

with

(numerical number) JTT 12 MOIS APAP =

CALCULATE (

      sum('EST_ JTT'[Key Account DDD MARCHE APAP CUMUL MOBILE ANNUEL]),

      DATESINPERIOD ( 'EST_ JTT'[Date PBI],        

                      [Last Date],  

                      -1,                  

                      MONTH                

      )

)

and

(% number) PDM 12 MOIS  =

CALCULATE (

      sum('EST_ JTT'[Key Account PDM DDD CUMUL MOBILE ANNUEL]),

      DATESINPERIOD ( 'EST_ JTT'[Date PBI],        

                      [Last Date],  

                      -1,                  

                      MONTH                

      )

)

In fact, on the table of this report, I have another column (which is also a measure) which has been calculated differently and which shows me the correct total.

I hope my problem is clear and that someone can help me.

Thank you very much

[v-huijiey-msft · Accepted solution]

Hi @laurarajao ,

According to your description, the reason why the correct Total value is not returned may be because the measure is created based on the context, and the Total calculation does not include the context of the categorical column, so the Total value is wrong.

The solution is to create another measure. The specific code is as follows:

sumx(values(Table[Category],[JTT 12 MOIS])

I also made some instructions on the SUMX and VALUES functions, I hope they can help you.

• SUMX(<table>, <expression>)——Sum

For example: =SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

This expression returns only the total freight charges in the InternetSales table for nternetSales[SalesTerritoryID] = 5 

• VALUES(<TableNameOrColumnName>) - When the input parameter is a column name, returns a single table containing distinct values in the specified column. Duplicate values are removed and only unique values are returned. BLANK value can be added. When the input parameter is a table name, returns the rows in the specified table. Keep duplicate rows. BLANK lines can be added.

Of course, if you want to learn more about these two functions, please click on the two links below:

SUMX function (DAX) - DAX | Microsoft Learn

VALUES function (DAX) - DAX | Microsoft Learn

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[bhanu_gautam]

@Greg_Deckler One more issue regarding total,