formula sum vs table sum

[rosefei]

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

[az38]

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]

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]

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]

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

Unfortunately this did not solve my prob. 

[Anonymous]

What issue are you facing?

[Chthonian]

Hi @rosefei ,

Looking at your description and your DAX, you seem to be using a measure that you have not defined "[TargetProfile]" - what is this calculation?

[rosefei]

Target = TargetProfile. Sorry I typed the table and did not type the name right