Displaying number of measures depending on their values

[Anonymous]

Hi,

I have a table with goals and achievements (real data have more levels than just group and id) :

group	id	goal	ach
A	a1	10	2
A	a2	5	7
A	a3	100	98
B	b1	60	25
B	b2	3	2
C	c1	5	7
C	c2	10	28

I must calculate a rate of achievement (achievement / goal).

I use a measure for that because I need to be able to aggregate this rate at mutliple levels :

mtx = DIVIDE(SUM(data[val]), SUM(data[obj]))

This is working as expected.

Depending on this rate, I then need to classify the data : 

• rate < 50% == bad
• 50% <= rate < 100% == correct
• 100% <= rate == good

I use another measure :

cat = SWITCH(TRUE(), [mtx] < 0.5, "bad", [mtx] < 1, "correct", "good")

Now, I have something like :

group	id	goal	ach	mtx	cat
A	a1	10	2	20%	bad
A	a2	5	7	140%	good
A	a3	100	98	98%	correct
B	b1	60	25	41.67%	bad
B	b2	3	2	66.67%	correct
C	c1	5	7	140%	good
C	c2	10	28	280%	good

If I aggregate by group, the values are correctly calculated :

group	goal	ach	mtx	cat
A	115	107	93.04%	correct
B	63	27	42.86%	bad
C	15	35	233.33%	good

Finally, I'd like to display the number of bad, correct and good depending on the filter I may have.

For example, If I only display group A (using a segment), I need to have one correct and zero bad or good.

But all my tests were unsuccessful. Usually, I end up counting the number of category on the lowest level of the table (a1, a2, ...) and not on the group level. I did also try to create another table with SUMMARIZECOLUMNS but didn't manage to make it work either.

I attached a simple pbix with some data here : test_cat.pbix

Thank you for your help.

[Anonymous]

Hi,

thank you both for the examples. I'll try to implement that in my report and let you know.

Both approachs are interesting ; I'm just a bit afraid of all that ADDCOLUMNS functions (they will manipulate a lot of data).

(I put the file back online just in case)

[tamerj1]

Hi @Anonymous 
The link is not working. However you can try the following

Correct =
VAR T1 =
    ADDCOLUMNS ( Data, "@mtx", [mtx] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@cat",
            SWITCH ( TRUE (), [@mtx] < 0.5, "bad", [mtx] < 1, "correct", "good" )
    )
VAR Result =
    SUMX ( T2, IF ( [@cat] = "correct", 1 ) )
RETURN
    Result

Good =
VAR T1 =
    ADDCOLUMNS ( Data, "@mtx", [mtx] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@cat",
            SWITCH ( TRUE (), [@mtx] < 0.5, "bad", [mtx] < 1, "correct", "good" )
    )
VAR Result =
    SUMX ( T2, IF ( [@cat] = "good", 1 ) )
RETURN
    Result

Bad =
VAR T1 =
    ADDCOLUMNS ( Data, "@mtx", [mtx] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@cat",
            SWITCH ( TRUE (), [@mtx] < 0.5, "bad", [mtx] < 1, "correct", "good" )
    )
VAR Result =
    SUMX ( T2, IF ( [@cat] = "bad", 1 ) )
RETURN
    Result

[lbendlin · Accepted solution]

you cannot measure a measure. You will have to create an entirely new measure for that second scenario, and use aggregator functions.

Table = UNION(ROW("Category","bad"),ROW("Category","correct"),ROW("Category","good"))

Measure = 
var a=ADDCOLUMNS(values(data[id]),"m",[cat])
return COUNTROWS(filter(a,[m]=SELECTEDVALUE('Table'[Category])))

See attached.