Forum Discussion

SeungLee95's avatar
SeungLee95
Icon for Microsoft Employee rankMicrosoft 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,"#"))
SeungLee95's avatar
SeungLee95
Icon for Microsoft Employee rankMicrosoft 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
Icon for Super User rankSuper 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
    Icon for Microsoft Employee rankMicrosoft 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!