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 wh...
Need Help
Tips and Tricks
Tutorial Requests
  • 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,"#"))
lbendlin's avatar
lbendlin
Super User
1 year ago

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

SeungLee95's avatar
SeungLee95
Microsoft Employee
1 year ago

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's avatar
    lbendlin
    Super User
    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,"#"))
    • SeungLee95's avatar
      SeungLee95
      Microsoft Employee
      1 year ago

      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!