Forum Discussion

anacpint's avatar
anacpint
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(DISTINCT...
  • anacpint's avatar
    anacpint
    4 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.

anacpint's avatar
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's avatar
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.

 

 

  • anacpint's avatar
    anacpint
    Helper I
    4 years ago

    This was the result 😞

     

    Maybe with this is easier to understand what I need:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        anacpint
        Helper I
        4 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.