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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
anacpint
Helper I
Helper I

Problem with total value

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:
anacpint_0-1660210316715.png

 

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,

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

View solution in original post

8 REPLIES 8
Endurion
Helper I
Helper I

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

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.

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_0-1660225116715.png

This was the result 😞

 

Maybe with this is easier to understand what I need:

anacpint_1-1660225938969.png

 

Anonymous
Not applicable

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] )

yingyinr_1-1660790496170.png

 

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

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
Helper I
Helper I

Perhaps this works

 

Measure =

SUMX (

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

    [Original measure]

)

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.