Forum Discussion
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) BelowTargetDepth = if([Rating]<[TargetProfile],[TargetProfile]-[Rating],0)
And a meausre:
TargetIndex = SUM([BelowTargetDepth])*COUNT([SubCategoryID])
When I put a table together, the sum is different... how do I write the formula so that the sum on each row is the same as the bottom?
| service | category | target | count of rating (below target) | sum of depth | TargetIndex = count of rating * sum of depth |
| A | C1 | 3 | 2 | 1 | 2 |
| A | C1 | 3 | 1 | 1 | 1 |
| A | C1 | 3 | 1 | 2 | 2 |
| A | C1 | 3 | 1 | 1 | 1 |
| A | C1 | 3 | 5 | 5 | 25 |
sum at the table: 10 10 100
You can tell the column TargetIndex does not add to 100... I am confused.. Thank you for your help!!
7 Replies
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...
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.
Target = TargetProfile. Sorry I typed the table and did not type the name right
