Total using SUMX() and calculated table is not correct

Question

I have a measure that used SUMX and calculated table. How can I get a total of column "Open" that is created within calculated table. On a picture you see 12, which is incorrect. The correct number is 335.TestMeasure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                SumByClaim,
                SumByClaim[TransactionDate] &gt;= FIRSTDATE ( SumByClaim[TransactionDate] )
                    &amp;&amp; SumByClaim[TransactionDate] &lt;= LASTDATE ( SumByClaim[TransactionDate] )
            ),
            SumByClaim[ClaimNumber],
            SumByClaim[ClaimantID],
            SumByClaim[ClaimStatus],
            "MaxTransDate", MAX ( SumByClaim[TransactionDate] )
        ),
        "Open", IF (
            SumByClaim[ClaimStatus] = "Open"
                &amp;&amp; [MaxTransDate] = MAX ( SumByClaim[TransactionDate] ),
            1,
            0
        )
    ),
    [Open]
)&nbsp;

amitchandak · Answer

In the filter these is no join withSumByClaim[ClaimNumber],
            SumByClaim[ClaimantID]&nbsp;Is it by design. You should take them in group by and also have max measure of SumByClaim[ClaimNumber],SumByClaim[ClaimantID] to join in filter clause as per need. Think that as second table, you need to put join clause to match dates.&nbsp;Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @ Thanks. My Recent Blog -https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739 https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

anonymous · Answer

Thanks. But sorry, I didn't quite understand what do you mean? Do I need to group by in my FILTER?

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
There should be some wrong with logic of the "IF" in your measure. Could you please share some sample data and clarify more details about the expected result?
&nbsp;
Regards,
Jimmy Tao

anonymous · Answer

The sample .pbix file could be found here:&nbsp;https://www.dropbox.com/s/9dsnylng70t5a8i/Count%20Open%20and%20Closed%20at%20Point%20of%20time.pbix?dl=0&nbsp;Thank you&nbsp;

Forum Discussion

Total using SUMX() and calculated table is not correct

4 Replies

Related content

SumX

Calculating correct Average

Correcting Data

SUMX

Calculation sumX multiplication