Problem with total value

[anacpint]

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,

[Endurion]

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

[anacpint]

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.

[Endurion]

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.

[anacpint]

This was the result 😞

Maybe with this is easier to understand what I need:

[Anonymous]

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

[anacpint · Accepted solution]

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.

[Endurion]

Perhaps this works

Measure =

SUMX (

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

    [Original measure]

)

[anacpint]

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