Forum Discussion

rosefei's avatar
rosefei
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

formula sum vs table sum

Hi all,   I have a rating table that have the following columns: 1) service 2) categoryID 3) subcategoryID 4) rating 5) target 6) BelowTarget = IF(rating<target, "Yes", "No) 7) BelowTargetDe...
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

hi rosefei 

i could not to repeat your data model, but it looks like task for SUMX() function, like

TargetIndex = SUMX('Table',CALCULATE(SUM([BelowTargetDepth]))*CALCULATE(COUNT([SubCategoryID])) )

 

  • rosefei's avatar
    rosefei
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Very interesting! I think we are close. Still have a small prob. 

     

    Now the targetIndex in each row is not doing the multiplication any more. I think this is because the underlying table is on subcategory level, the SUMX is only doing on row level sun, which means the count(subcategroyID) is always 1. 

     

    I tried a few different combination but still not getting the right answer...

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      in sumx if you want to multiply column with total count you need to use all.

       

      COunt measure=calculate(count(table[Column]),all(table[Column]))

       

      Then use it in sumx.

      SumX(table,table[Col1]*[Count measure])

       

      Refer microsoft documentation

      https://docs.microsoft.com/en-us/dax/all-function-dax

       

      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.

      • rosefei's avatar
        rosefei
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Thank you for your input, I tried to respond multiple times but the website kept crashing. 

         

        Unfortunately this did not solve my prob.