Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dijitald
Employee
Employee

Incorrect Total on custom PowerBI Measure

I'm struggling trying to get my Total correct for my custom Measure in Power BI. I've seen the postings for the solution using SUMX, and I've gotten a lot further, but still stumped. When I'm using the SUMX function, my TOTAL is exactly double what it should be. Here is the code:

 

Measure 3 = 
IF(ISFILTERED('Milestone'[ID]),
    IF(NOT(ISBLANK(CALCULATE(MAX('Milestone'[Estimated Date])))),
        IF(SELECTEDVALUE('Milestone'[Non Recurring]) = "Yes", 1, 2)
    ),
    SUMX(
        VALUES('Milestone'),
        IF('Milestone'[Non Recurring] = "Yes", 1, 2)
    )
)

 

The top portion of the ISFILTERED statement works fine for each of the individual items. However the TOTAL is always double.

Any help is greatly appreciated!

 

EDIT

I should mention that this measure is used on the Child table of a Parent/Child relationship on the form. So the list of Milestones is filtered, and correctly displayed in the Child table. Now I'm realizing that when i simplify the SUMX function to just contain 

SUMX('Milestone', 1)

I get exactly 2x the number of related Children. So if there are 2 Milestones, the total will be 4. If there are 6 Milestones, the total will be 12. So for some reason, it seems to be iterating over the collection twice? 

I'm stumped...

 

 

2 REPLIES 2
PijushRoy
Community Champion
Community Champion

Hi @dijitald 

Please share sample data

Unfortunately, this is sensitive data and i can't share. I did create a simple sample, but it doesn't exhibit the same issue so i can't reproduce this. Not sure what that means for my dataset. I did add some more details above so let me know if you have any ideas!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.