Apply SUM Total to a Conditionally Formatted Column

[SeungLee95]

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

Stamp	APType	TEST
XXXXX-XXXXXXX	Classic	N/A
YYYYY-YYYYYYY	Converged	13
YYYYY2-YYYYYY2 (more value below)	Converged	7
YYYYY3-YYYYYY3 (more value below)	Converged	6

Thank you very much!

[lbendlin]

the screenshots are too small.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SeungLee95]

Hi @lbendlin - Thank you for the note, I did not notice that the images were too small when I was creating it. Apologies for that.

I've tried adding a table of what I mean but since I'm dealing with a Matrix the screenshots felt like it was the best way to explain while adding the queries in it.

I refrained from uploading the pbix file or any other queries, since the point is to avoid sensitive data as you advised.

Please let me know if this helps or if there's some other way I could help provide more information.

[lbendlin]

You cannot mix text and numbers in your output unless you format your numbers as text too.

[SeungLee95]

Thank you @lbendlin,

And in that case if the numbers were text too then the SUM wouldn't be possible anyways since it's of text format right?

Appreciate the clarification/confirmation on this.

[lbendlin]

you can still do your math to arrive at the totals but the output format of that math needs to be text.

[SeungLee95]

@lbendlin - May I ask how/what you mean on how to achieve that?

I don't think there'd be any issue if the output format was changed if it was consistent (for every row/level that it's showing for)

[lbendlin · Accepted solution]

Test Sum =

var s = SUMX(ADDCOLUMNS('Table',"s",if([APType]="Classic",0,VALUE([TEST]))),[s])

RETURN if(s=0,"N/A",FORMAT(s,"#"))

[SeungLee95]

Thank you @lbendlin ,

I was able to modify that query:

TESTSum = 

VAR hasClassic = COUNTROWS(FILTER('COUDashboard', [APType] = "Classic")) > 0
VAR s = SUMX(FILTER('COUDashboard', [APType] <> "Classic"), VALUE([ServiceReadiness]))
RETURN IF(hasClassic, "N/A", FORMAT(s, "#"))

And I'm now seeing the TESTSum added correctly (reflects SR values) while also showing "N/A" on Classic Capacity.

The issue I had with the original proposed query was that some columns had a 0/null by default even if their [APType] wasn't "Classic" so it was showing N/A and sums combined for other types too.

Using your query I was able to just modify it a bit and achieve exactly what I was looking for.

Thank you very much!