Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all.
this is table a
INSTALL | SETTING | INSTANCES |
one | a | 350 |
one | b | 100 |
one | c | 50 |
two | a | 300 |
two | b | 200 |
using table a - i want a column that shows me the total instances for an install (INSTALL INSTANCES) and another column that shows me the instances by setting divided by the total instances for install (SCORE) to get to below table b
INSTALL | SETTING | INSTANCES | INSTALL INSTANCES | SCORE |
one | a | 350 | 500 | 70% |
one | b | 100 | 500 | 20% |
one | c | 50 | 500 | 10% |
two | a | 250 | 400 | 63% |
two | b | 150 | 400 | 38% |
and then finally i need 1 more column that shows me the highest score (TOP SCORE) for each install (table c)
INSTALL | SETTING | INSTANCES | INSTALL INSTANCES | SCORE | TOP SCORE |
one | a | 350 | 500 | 70% | 70% |
one | b | 100 | 500 | 20% | 70% |
one | c | 50 | 500 | 10% | 70% |
two | a | 250 | 400 | 63% | 63% |
two | b | 150 | 400 | 38% | 63% |
I have been playing around with calculated tables, sums, distinct counts etc for days..... anyone able to help?
Solved! Go to Solution.
Try this to Top Score:
Top Score =
VAR _MaxInstances =
MAXX(
ALLSELECTED(T_Instances[SETTING]),
[Count Instances] // This is a measure of COUNT(T_Instances[Setting]
)
VAR _SumSettings = [Sum Settings]
VAR _Result = _MaxInstances / _SumSettings
RETURN
_Result
Proud to be a Super User!
Almost there - i forgot to mention that the instances is currently just a count column of Setting in the visual,
So i am struggling to translate your measure of Sum Instances - i assume i now need to create a sum or count column or measure as part of the original table so that can be referenced in the measure Sum Instances?
I have tried making a column that is just count(Instances) but its value is way off of what it should be when in the visual. i assume it is counting everything and not just the instances for that install
Install | Setting | Count of Setting (Should be same as Sum of Instances) | Sum of Instances | Sum Instances | Sum Settings | Score | Top Score |
One | null | null | 242458 | 242458 | 51643554 | 0.47% | 0.23% |
One | A | 2 | 242458 | 242458 | 51643554 | 0.47% | 0.23% |
One | B | 1 | 121229 | 121229 | 51643554 | 0.23% | 0.23% |
One | C | 117 | 14183793 | 14183793 | 51643554 | 27.46% | 0.23% |
One | D | 5 | 606145 | 606145 | 51643554 | 1.17% | 0.23% |
One | E | 3 | 363687 | 363687 | 51643554 | 0.70% | 0.23% |
One | F | 1 | 121229 | 121229 | 51643554 | 0.23% | 0.23% |
One | G | 286 | 34671494 | 34671494 | 51643554 | 67.14% | 0.23% |
One | H | 9 | 1091061 | 1091061 | 51643554 | 2.11% | 0.23% |
Try to switch this funtion SUM(T_Instances[INSTANCES])
To: COUNT(T_Instances[Setting]
Proud to be a Super User!
that worked - but Top Score is not working..
this is what is now presented
Install | setting | Sum Instances | Sum Settings | Score | Top Score |
One | null | null | 424 | 28591.75% | |
One | a | 2 | 424 | 0.47% | 28591.75% |
One | b | 1 | 424 | 0.24% | 28591.75% |
One | c | 117 | 424 | 27.59% | 28591.75% |
One | d | 5 | 424 | 1.18% | 28591.75% |
One | e | 3 | 424 | 0.71% | 28591.75% |
One | f | 1 | 424 | 0.24% | 28591.75% |
One | g | 286 | 424 | 67.45% | 28591.75% |
One | h | 9 | 424 | 2.12% | 28591.75% |
Try this to Top Score:
Top Score =
VAR _MaxInstances =
MAXX(
ALLSELECTED(T_Instances[SETTING]),
[Count Instances] // This is a measure of COUNT(T_Instances[Setting]
)
VAR _SumSettings = [Sum Settings]
VAR _Result = _MaxInstances / _SumSettings
RETURN
_Result
Proud to be a Super User!
Brilliant. Works a treat
Do you have any suggestions as to how i could Table Just the Install and Top Score?
Install | Top Score |
One | 70% |
Two | 63% |
... | ... |
I don't understand your question. If you only put the Instal column and the Top Score on a table visual you will have that table
Proud to be a Super User!
Long night 😞 - didnt realise it would still work just fine when other columns where then removed. thank you so much for your help.
You're welcome. Please, next time provide all the information at the beginning for a more effective and faster solution.
Thank you
Proud to be a Super User!
Hi,
Here is my solution:
Measures:
Sum Instances = SUM(T_Instances[INSTANCES])
Sum Settings =
CALCULATE(
[Sum Instances],
ALLSELECTED(T_Instances[SETTING])
)
Score = [Sum Instances] / [Sum Settings]
Top Score =
VAR _MaxInstances =
CALCULATE(
MAX(T_Instances[INSTANCES]),
ALLEXCEPT(T_Instances,T_Instances[INSTALL])
)
VAR _SumSettings = [Sum Settings]
VAR _Result = _MaxInstances / _SumSettings
RETURN
_Result
Table configuration:
Please let me know if this solve your problem.
Proud to be a Super User!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |