topic Re: Problem with total value in DAX Commands and Tips

Problem with total value

anacpint — Thu, 11 Aug 2022 09:34:12 GMT

Hello,

I have the following measure:

OTD_TESTE = IF([COUNTReport]=2,

CALCULATE(DIVIDE(SUM(FactOTDJnJPap[measurevalue]),2)), SUM(FactOTDJnJPap[measurevalue]))

COUNTReport = CALCULATE(DISTINCTCOUNTNOBLANK(FactOTDJnJPap[IDTypeReportJnJPharma]), DimTypeReport[type_of_report] IN {"CPV", "PQR"})

This is the result:

How can I solve the issue with the total value? I've checked the documentation about using IF(HASONEVALUE but I couldn't found a way to solve this.

Thanks is advance,

Re: Problem with total value

Anonymous — Thu, 11 Aug 2022 09:58:15 GMT

Perhaps this works

Measure =

SUMX (

GROUPBY ( 'Table', 'Table'[level1field], 'Table'[level2field] ),

[Original measure]

)

Re: Problem with total value

anacpint — Thu, 11 Aug 2022 10:16:48 GMT

Didn't work. The result on the totals are even more different than before.

Re: Problem with total value

Anonymous — Thu, 11 Aug 2022 13:16:25 GMT

Can you show me what the formule is that you have made?

Re: Problem with total value

anacpint — Thu, 11 Aug 2022 13:25:38 GMT

I already tried a lot of differents but one of them was: SUMX(GROUPBY(FactOTDJnJPap, FactOTDJnJPap[IDTypeReportJnJPharma], FactOTDJnJPap[IDSiteJnJPharma]),IF([COUNTReport]=2,

CALCULATE(DIVIDE(SUM(FactOTDJnJPap[measurevalue]),2)), SUM(FactOTDJnJPap[measurevalue])).

What I'm trying to do is: check the distinct Type of reports, if they are 2 then divide the sum of the measure by two, if 1 just sum the measure value, considering just two types of report (CPV and PQR). In the total value I need to see the sum of that calculations, just for CPV and PQR.

Re: Problem with total value

Anonymous — Thu, 11 Aug 2022 13:31:21 GMT

Could you try this (messurement);

VAR Counting = IF([COUNTReport]=2, CALCULATE(DIVIDE(SUM(FactOTDJnJPap[measurevalue]),2)), SUM(FactOTDJnJPap[measurevalue]))

RETURN

SUMX(GROUPBY(TABLE, jnj_site), Counting)

Please note that I do not know the table of jnj_site.

Re: Problem with total value

anacpint — Thu, 11 Aug 2022 13:52:32 GMT

This was the result 😞

Maybe with this is easier to understand what I need:

Re: Problem with total value

Anonymous — Thu, 18 Aug 2022 02:42:08 GMT

Hi anacpint ,

You can create another new measure as below and put this new measure to replace the original measure [OTD_TESTE] onto the table visual as below screenshot...

Measure = SUMX ( VALUES ( FactOTDJnJPap[jnj_site] ), [OTD_TESTE] )

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

Re: Problem with total value

anacpint — Wed, 24 Aug 2022 08:52:50 GMT

Thank you for your answer.

This was the solution for the problem:

OTD_TESTE =
SUMX (
SUMMARIZE ( FactOTDJnJPap, DimSite[jnj_site], DimTypeReport[type_of_report] ),
CALCULATE (
IF (
[COUNTReport] = 2,
DIVIDE ( SUM ( FactOTDJnJPap[measurevalue] ), 2 ),
SUM ( FactOTDJnJPap[measurevalue] )
),
FactOTDJnJPap[IDTypeReportJnJPharma] IN { 1, 5 }
)
)

Best Regards, Ana.