Forum Discussion
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
| 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!
- 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
- lbendlinSuper User
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- SeungLee95Microsoft Employee
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.
- lbendlinSuper User
You cannot mix text and numbers in your output unless you format your numbers as text too.