Lost in counts, summarize etc. when trying to create tables. help needed

[TK421]

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?

[TK421]

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

[TK421]

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%

[_AAndrade]

Try to switch this funtion SUM(T_Instances[INSTANCES])

To: COUNT(T_Instances[Setting]

[TK421]

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%

[_AAndrade · Accepted 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

[TK421]

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

[_AAndrade]

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

[TK421]

Long night 😞 - didnt realise it would still work just fine when other columns where then removed. thank you so much for your help. 

[_AAndrade]

You're welcome. Please, next time provide all the information at the beginning for a more effective and faster solution.
Thank you

[_AAndrade]

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.