Total value coming from measure is wrong

[anacpint]

Hello,

I developed this measure:

CALCULATE(if([COUNTReport]=2, DIVIDE(SUM(FactOTDJnJPap[measurevalue]),2), SUM(FactOTDJnJPap[measurevalue])), FactOTDJnJPap[IDTypeReportJnJPharma] IN {1,5})

The row values are correct but the total is wrong, the value should be around 9,2.

In the next image I explain better what I have now coming from the Fact and what I need to calculate.

I have a line chart with the values for every Site (this one is showing the correct values) and another one with the total value divided by the number of sites (it's not always the same number) for that month.

Really appreciate your help, I'm stucked with this for 2 days.

Thanks is advance.

[tamerj1]

Hi @anacpint 

please try

OTD_TESTE =
SUMX (
    VALUES ( FactOTDJnJPap[jnj_site] ),
    CALCULATE (
        IF (
            [COUNTReport] = 2,
            DIVIDE ( SUM ( FactOTDJnJPap[measurevalue] ), 2 ),
            SUM ( FactOTDJnJPap[measurevalue] )
        ),
        FactOTDJnJPap[IDTypeReportJnJPharma] IN { 1, 5 }
    )
)

[anacpint]

Hi, Thank you for your answer!

Didn't work, the values still the same...

[tamerj1]

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

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 · Accepted solution]

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]

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