Why is still Blank

Hi Anonymous ,

 

Your ORGrowth Calculation measure might be returning blank due to a few potential issues. First, ensure that "SelectedDivision" is actually being selected. The SELECTEDVALUE function can return blank if multiple values are selected or if there is no value in the filter context, causing the SWITCH statement to skip the "CPV" condition. Adding a default value can help prevent this issue:

VAR SelectedDivison = SELECTEDVALUE('Power Bi'[Divison], "None")

Another potential issue is the filtering logic for "Division". The condition:

ISFILTERED('Power Bi'[Divison]) && SelectedDivison = "CPV"

might not be working as expected because ISFILTERED only checks if a filter exists but does not verify if the selected value matches "CPV". Instead, just checking SelectedDivison = "CPV" ensures that it is properly evaluated even when there’s only one selected value.

Additionally, the condition for filtering "SAP Code" may not be matching exactly. If "Grand Total of CPV" does not exist in 'Power Bi'[SAP Code], the calculation will return blank. To handle potential mismatches or extra spaces, consider using CONTAINSSTRING for partial matching:

CALCULATE(
    SUM('Power Bi'[Growth vsYTD 24 (OR LOCAL + INDENT)]),
    CONTAINSSTRING('Power Bi'[SAP Code], "Grand Total of CPV")
)

If the measure still returns blank, it could be due to missing data in the calculation. Testing with a simple count measure like:

Test_Measure = COUNTROWS('Power Bi')

can confirm whether any rows match the applied filters. If Test_Measure returns 0, then no rows are being returned, which explains why the measure remains blank. To handle such cases, modify the measure to replace blank values with 0:

IF(
    ISBLANK(
        CALCULATE(SUM('Power Bi'[Growth vsYTD 24 (OR LOCAL + INDENT)]))
    ),
    0,
    CALCULATE(SUM('Power Bi'[Growth vsYTD 24 (OR LOCAL + INDENT)]))
)

A final optimized version of your measure could look like this:

ORGrowth Calculation =
VAR SelectedPerson = SELECTEDVALUE('Power Bi'[Name])
VAR SelectedDivison = SELECTEDVALUE('Power Bi'[Divison], "None")

RETURN
SWITCH(
    TRUE(),
    NOT ISFILTERED('Power Bi'[Name]) && NOT ISFILTERED('Power Bi'[Divison]),
        CALCULATE(
            SUM('Power Bi'[Growth vsYTD 24 (OR LOCAL + INDENT)]),
            'Power Bi'[SAP Code] = "Grand Total CPD SEA Excl. IDC&AUI"
        ),

    SelectedPerson <> BLANK(),
        CALCULATE(
            SUM('Power Bi'[Growth vsYTD 24 (OR LOCAL + INDENT)]),
            'Power Bi'[Name] = SelectedPerson
        ),

    SelectedDivison = "CPV",
        CALCULATE(
            SUM('Power Bi'[Growth vsYTD 24 (OR LOCAL + INDENT)]),
            CONTAINSSTRING('Power Bi'[SAP Code], "Grand Total of CPV")
        ),

    0
)

If the measure is still blank, adding a Card visual displaying SelectedDivison and SelectedPerson can help verify whether they return expected values. Additionally, using COUNTROWS('Power Bi') inside a measure can confirm if any rows are returned after filtering. Finally, checking the 'Power Bi'[SAP Code] column in the data view ensures that "Grand Total of CPV" actually exists in the dataset.

 

Best regards,