Forum Discussion

anacpint's avatar
anacpint
Icon for Helper I rankHelper I
4 years ago
Solved

Total value coming from measure is wrong

Hello,   I developed this measure: CALCULATE(if([COUNTReport]=2, DIVIDE(SUM(FactOTDJnJPap[measurevalue]),2), SUM(FactOTDJnJPap[measurevalue])), FactOTDJnJPap[IDTypeReportJnJPharma] IN {1,5}) ...
DAX
DAX Calculation
Total Measure
Total not matching
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi anacpint 
    Pleas try 

    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 }
    )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

anacpint 
It is critical that you provide complete and accurate information. This solution is based on the screenshot of the table visual that you've provided. In case you have other columns/tables invloved in this table either directly or indirectly (via slicers, visual filters or page filters) please let me know.

anacpint's avatar
anacpint
Icon for Helper I rankHelper I
4 years ago

So, for this situation, I'm using a fact table named FactOTDJnJPap (which contains the measure values calculated on the db side), a DimTypeReport, a DimSite and a DCalendar. I use this Dim's to filter different visuals. For this specific measure (OTD) I have two graphics:

For both of them I need to do the calculations that I mentioned before, in the first one I have a DAX filter to show just one of the type of reports, but the measures are the same for the 3 lines. When I select on the filter panel just one Site, the values are correct, when I select all of them, the values are wrong. As filter page I have DimTypeReport[type_of_report] is CPV or PQR.

 

Thank you very much.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi anacpint 
    Pleas try 

    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 }
    )
)
    • anacpint's avatar
      anacpint
      Icon for Helper I rankHelper I
      4 years ago

      tamerj1 It worked!! Thank you very much for your help. You saved my day 😄