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



maruthisp's avatar
maruthisp
Super User
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