Use Subtotal Sum in Matrix for a Dynamically 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?

Thank you very much!

[v-echaithra]

Hi @SeungLee95 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @SeungLee95 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra · Accepted solution]

Hi @SeungLee95 ,

Create a clean numeric measure for calculation.

SR_Actual =
IF (
SELECTEDVALUE(COUDashboard[APType]) = "Classic",
BLANK(),
SUM(COUDashboard[ServiceReadiness])
)

Use a second measure for display using format string or Conditional Formatting.
Instead of showing "N/A" directly in the DAX measure, use Conditional Formatting to display "N/A" as a custom string for rows with APType = "Classic".
Create a DAX measure just for formatting, but you can’t use this for subtotaling, it’s for visual override only.

DisplayLabel =
IF (
SELECTEDVALUE(COUDashboard[APType]) = "Classic",
"N/A",
FORMAT([SR_Actual], "#,0")
)

Now create a Matrix
Add the SR_Actual measure to your Matrix.
Click the measure in the Values area > click the arrow > Conditional formatting > Field value.
Choose "Based on field" > DisplayLabel.
Select to apply to "Values only".

Set the output as custom text,  Power BI will now use "N/A" for classic rows and actual numbers elsewhere.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Chaithra E

[v-echaithra]

Hi @SeungLee95 ,
Thank you for reaching out to Microsoft Fabric Community.

Power BI cannot sum string values like "N/A", and it tries to treat the results of your measure as discrete values, which impacts the subtotal calculation.
To resolve this, you need to adjust your DAX measure so that the "N/A" string doesn't interfere with the aggregation, while still ensuring the visual shows "N/A" when the condition is met.

Try this modified DAX:

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

Instead of messing with your main measure, you can just apply this conditional formatting to make sure everything looks good in the table. So, the rows that should say "N/A" will show that, and the numbers will still be properly totaled in the subtotal without causing any issues. You can use conditional formatting directly in the Matrix visual to handle this. Basically, you can create either a measure or a calculated column that checks if the row is "Classic." If it is, it’ll return "N/A" for that row. This way, the "N/A" will only show up in the rows where it’s needed, and the numeric values will still get summed up correctly for the subtotals.

CF_SR =
IF (
coudashboard[APType] = "Classic",
"N/A",
coudashboard[Servicereadiness]
)

You can use this column for visual display and ensures that you aren't directly changing the measure, so aggregation (SUM) won't be affected.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Chaithra E

[SeungLee95]

Hi Chaithra!

Thank you very much for the guidance. However, I'm a little confused.

I tried your suggestion on changing the Query for TEST but I don't really see any changes in the totaled Sum

TEST and SR should be totaled to the same but I don't specifically see any changes.

As for the suggestion on the Conditional Formatting, I'm not quite understanding where I can achieve this. Are you talking about creating a new column with this query?
IF (
coudashboard[APType] = "Classic",
"N/A",
coudashboard[Servicereadiness]
)

Or is this applicable somewhere else to the test column?

Thank you again!