Calculate Countrows Issue wrong number displayed

[SamuelSea]

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 breakdown my steps for you guys. Is there a simpler way to count the total number of funded projects?

Count of Funded projects

Count of Funded Measure =

CALCULATE(

    COUNTROWS('Project Summar'),

    'Project Summar'[Budget Status] = "Funded"

This is the correct table  Which shows 12 funded projects.

This is the correct measure I used to get it.

Funding Status  (Measure)

Status Go =

SWITCH(

    TRUE(),

    ISBLANK([Total Actual]) && ([Total Budget]) > 0, "Not Started",

    [Total Actual] = 0 && [Total Budget] = 0, "Not Funded",

    [Total Actual] > [Total Budget], "Over Funded",

    [Total Actual] = [Total Budget], "On Budget",

    [Total Actual] < [Total Budget] && [Total Actual] > 0, "Funded","Unbudgeted Credit"

)

When I tried to use a measure formula in my first calculation it would not let me.

This was my work around that resulted in the inaccurate number of 17 funded projects

Step One: Project Summary (Table)

Project Summar =

SUMMARIZE(

    'Research Capital Report',

    'Research Capital Report'[Project Name],

    "Total Budget", CALCULATE(SUM('Research Capital Report'[Budget Amt]), 'Research Capital Report'[Type ] = "Budget"),

    "Total Actual", CALCULATE(SUM('Research Capital Report'[Total Amt]), 'Research Capital Report'[Type ] = "Actual"

))

Step Two: Budget Status:

Budget Status =

SWITCH(

    TRUE(),

    ISBLANK([Total Actual]) && [Total Budget] > 0, "Not Started",

    [Total Actual] = 0 && [Total Budget] = 0, "Not Found",

    [Total Actual] > [Total Budget], "Over Funded",

    [Total Actual] = [Total Budget], " On Budget",

    [Total Actual] < [Total Budget] && [Total Actual] > 0, "Funded", "Unbudgeted Credit"

)

Step Three: Count of Funded projects

Count of Funded Measure =

CALCULATE(

    COUNTROWS('Project Summar'),

    'Project Summar'[Budget Status] = "Funded"

This is where it shows 17 projects that have been funded.  But when you look at Total Actual and Total Budget numbers are totally incorrect. 

Thank you so much!

Samuel 

[V-yubandi-msft]

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'.

[maruthisp · Accepted solution]

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

[d_m_LNK]

Are the "Total Actual" and "Total Budget" columns also measures?  If so something with these might be causing the issue as your budget status measure is accurately assigning the statuses based on your logic you have.  Something with how those totals are calculated might be causing the issue.  If you post the DAX for those that would help as well. 

Another thing you could try to fix your error above is perist your measure in a variable for this code:

The change would be:

Count of Funded T1 = 

VAR StatusGo = Research Capital Report[Status Go]
**or if this is not a measure**

VAR StatusGo = SelectedValue(Research Capital Report [Status Go])

RETURN

CALCULATE(

     COUNTROWS('Project Summar'),

     StatusGo = "Funded")

[SamuelSea]

@d_m_LNK 

 I tried your solution but kept on getting the "A Function PLACEHOLDER message”

Both are measures.

Total Actual = Sum('Research Capital Report'[Total Amt])

Total Budget = Sum('Research Capital Report'[Budget Amt])