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...
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.