topic Re: Displaying number of measures depending on their values in DAX Commands and Tips

Displaying number of measures depending on their values

Anonymous — Mon, 07 Mar 2022 08:58:48 GMT

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.

Re: Displaying number of measures depending on their values

lbendlin — Sat, 05 Mar 2022 03:23:01 GMT

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.

Re: Displaying number of measures depending on their values

tamerj1 — Sun, 06 Mar 2022 18:06:19 GMT

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

Re: Displaying number of measures depending on their values

Anonymous — Mon, 07 Mar 2022 09:14:21 GMT

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)