Total measure not working with Summarize

[calerof]

Hello,

I have a measure to calculate a subtraction and the total is not correct. I followed @Greg_Deckler 's Mesaure Totals - The Final Word post. 

This is my single measure code:

Uptime = 
CALCULATE(
    SUMX(
        cr4be_productiondailylog, 
        DIVIDE(
            cr4be_productiondailylog[cr4be_availabletime],
            COUNTA( cr4be_productiondailylog[cr4be_hour] )
        ) - [Downtime Total] )
)

This is my Total Measure code:

Uptime Total = 
VAR _table = SUMMARIZE(cr4be_productiondailylog, [cr4be_partnumber],"_value",[Uptime])
RETURN
IF(HASONEVALUE(cr4be_productiondailylog[cr4be_partnumber]), [Uptime], SUMX(_table, [_value]))

These are my pbix and data files:

pbix

data

This is what I'm getting:

I appreciate any help.

I'm wondering if I have to change the initial single measure for a simpler one to make it work.

Thanks,

Fernando

[MFelix · Accepted solution]

Hi @calerof,

You have two main issues on the formula for the total time:

• Context: When you are making the summarize and add the partnumber you get the calculation for that part number however and since you are using a SUMX the more detail you add if you don't add it to your SUMX  then you will get the calculations only at the top level.
• Using the Hasonevalue part in the IF statment can also be tricky since if in one of the lines in this case the partnumber has only one value it will pick up the first values on the SUMX that is why he is calculating the 185 and relating to the previous issue the calculations are incorrect.

Redo your total to the following measure:

VAR _table =
    SUMMARIZE (
        cr4be_productiondailylog,
        cr4be_productiondailylog[cr4be_workcenter],
        cr4be_productiondailylog[cr4be_mold],
        [cr4be_partnumber],
        cr4be_productiondailylog[cr4be_shiftname],
        "_value", [Uptime]
    )
RETURN
    IF (
        ISINSCOPE ( cr4be_productiondailylog[cr4be_partnumber] ),
        [Uptime],
        SUMX ( _table, [_value] )
    )

Check result below and in attach PBIX file.

[calerof]

Hi @MFelix ,

Thank you very much for your analysis, the explanation and help, I appreciate it a lot. That made me understand better the function SUMMARIZE.

Best,

Fernando