Forum Discussion

Helper I

4 years ago

Solved

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:

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,

anacpint
3 years ago
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.

8 Replies

Anonymous
Not applicable
4 years ago
Perhaps this works

Measure =
SUMX (
GROUPBY ( 'Table', 'Table'[level1field], 'Table'[level2field] ),
[Original measure]
)
- anacpint
  Helper I
  4 years ago
  Didn't work. The result on the totals are even more different than before.
Anonymous
Not applicable
4 years ago
Can you show me what the formule is that you have made?
- anacpint
  Helper I
  4 years ago
  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.
  - Anonymous
    Not applicable
    4 years ago
    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.