Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!
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.
Thank you for your input, I tried to respond multiple times but the website kept crashing.
Unfortunately this did not solve my prob.
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?
Target = TargetProfile. Sorry I typed the table and did not type the name right
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |