Forum Discussion

SeungLee95's avatar
SeungLee95
Microsoft Employee
1 year ago
Solved

Apply SUM Total to a Conditionally Formatted Column

I have the following column in my table called SR that has the following values:

Null / 1 

 

As seen on the table below, within the Matrix it'll calculate the Subtotal for each row based on the which row level it is being displayed.

 

 

 

 

The issue is that I created the following Measure with a dynamic format since those rows with type == Classic should show N/A for ease of data consumption while reflecting the SR value:

Measure:

TEST = IF (
    SELECTEDVALUE (COUDashboard[APType]) = "Classic",
    "N/A",
    SELECTEDVALUE(COUDashboard[ServiceReadiness])
)

 

Format:

IF ( SELECTEDVALUE ( COUDashboard[APType] ) <> "Classic", "#,0" )

 

As it can be seen in the table picture above, though, while the N/A is displayed correctly and "1" or null is as well, the subtotal isnt calculated correctly. (it should be the exact same as SR but obviously just shows "first" since I think it's calculated differently):

 

 

SR uses SUM available

 

Test does not have it as it is dynamically formatted ("N/A" string or numeric value)

 

 

Is there any way to achieve a SUM subtotal done correctly for a dynamically formatted measure? Or is this not possible in Power BI?

 

I've tried creating a new column/measure with the following but it has not really worked 

TEST =
IF(
selectedvalue(coudashboard[APType]) = "Classic",
Blank(),
selectedvalue(coudashboard[Servicereadiness])
)

 

Expected Result:

I can't really show the Matrix here, but for Stamp "XXXX..." the TEST would show N/A as it is of APType == Classic

While Stamp "YYYYY-..." and the rows under it, would all have "1" as their value until it adds up to 13

StampAPTypeTEST
XXXXX-XXXXXXXClassicN/A
YYYYY-YYYYYYYConverged13
YYYYY2-YYYYYY2 (more value below)Converged7
YYYYY3-YYYYYY3 (more value below)Converged6

Thank you very much!

  • lbendlin's avatar
    lbendlin
    1 year ago

    Test Sum =
    var s = SUMX(ADDCOLUMNS('Table',"s",if([APType]="Classic",0,VALUE([TEST]))),[s])
    RETURN if(s=0,"N/A",FORMAT(s,"#"))

8 Replies