Forum Discussion
Use Subtotal Sum in Matrix for a Dynamically formatted Column
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
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
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!
