Forum Discussion

SamuelSea's avatar
SamuelSea
Frequent Visitor
1 year ago
Solved

Calculate Countrows Issue wrong number displayed

Hello Everyone,   I am having issue with this calculate measure below.  It is showing me the total number of funded projects is 17 when in actual it should be 12.  Why is it doing this? I will brea...
Need Help
  • maruthisp's avatar
    maruthisp
    1 year ago

    Hi SamuelSea,


    As per understanding by reading your original post:

    The mismatch in numbers 17 vs. 12 is likely due to row context not being properly translated into filter context when using calculated columns and measures together.

    Instead of relying on the calculated column Budget Status, use the same logic directly in your measure using variables:

    Count of Funded Projects =
    CALCULATE(
        DISTINCTCOUNT('Research Capital Report'[Project Name]),
        FILTER(
            ADDCOLUMNS(
                VALUES('Research Capital Report'[Project Name]),
                "TotalBudget", CALCULATE(SUM('Research Capital Report'[Budget Amt]), 'Research Capital Report'[Type ] = "Budget"),
                "TotalActual", CALCULATE(SUM('Research Capital Report'[Total Amt]), 'Research Capital Report'[Type ] = "Actual")
            ),
            [TotalActual] < [TotalBudget] && [TotalActual] > 0
        )
    )


    Hope the above DAX expression may help you.

    Please let me know if you have further questions.

    If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

     

    Best Regards, 

    Maruthi 

    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X



V-yubandi-msft's avatar
V-yubandi-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi SamuelSea ,

Thanks for posting your query in the Microsoft Fabric Community.  I have successfully recreated your scenario using the sample dataset and logic as described in your requirements. I identified the key problem Budget Status was a measure, which made it difficult for Power BI to filter rows correctly within CALCULATE().

 

To fix this, I switched Budget Status to a calculated column, ensuring accurate row level filtering. Once I applied this change and corrected the measure logic, I got the expected result 5 funded projects, just as you anticipated.

FYI:

Sample Data:

 

 

I’ve attached a .pbix file with the fully working version for you to review. Feel free to check it out, and let me know if you need any further refinements.

 

Thank you all for your inputs to the community.

 

If my response solved your query, please mark it as the Accepted solution to help others find it easily. And if my answer was helpful, I'd really appreciate a 'Kudos'.