Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table with 5 columns – Cd_aluno X appears 20 times, Y 20 times and so on thousands of times. The RES_ITEM_A, RES_ITEM_B and RES_ITEM_C columns are the answers given to the 5 questions of each of the 5 pillars.
I need to create the categories according to the table below:
marking percentage | result per pillar evaluated |
most answers A (>=60%) | underdeveloped |
most answers B (>=60%) | moderately developed |
most answers C (>=60%) | very developed |
tie between A (40%) and B (40%) | underdeveloped |
tie between A (40%) and C (40%) | moderately developed |
tie between B (40%) and C (40%) | very developed |
I need this result of students by Pillar.
I don't know if I need to create an auxiliary table, could someone help?
Solved! Go to Solution.
Here's what worked in my test.
First, I've created 3 ratio measures: [A],[B], [C]:
A Ratio = DIVIDE(SUM('Table'[RES_ITEM_A]),COUNTROWS('Table'))
B Ratio = DIVIDE(SUM('Table'[RES_ITEM_B]),COUNTROWS('Table'))
C Ratio = DIVIDE(SUM('Table'[RES_ITEM_C]),COUNTROWS('Table'))
Now, I've created the 3 category measures:
Underdevelopd =
SUMX(
VALUES( 'Table'[Cd_aluno] ),
IF(
OR(
[A Ratio] >= 0.6,
AND(
[A Ratio] = [B Ratio],
[A Ratio] = 0.4
)
),
1,
0
)
)
Moderately Developed =
SUMX(
VALUES( 'Table'[Cd_aluno] ),
IF(
OR(
[B Ratio] >= 0.6,
AND(
[A Ratio] = [C Ratio],
[A Ratio] = 0.4
)
),
1,
0
)
)
Very Developed =
SUMX(
VALUES( 'Table'[Cd_aluno] ),
IF(
OR(
[C Ratio] >= 0.6,
AND(
[B Ratio] = [C Ratio],
[C Ratio] = 0.4
)
),
1,
0
)
)
And now you can toss them into a 100% stacked bar chart:
Thank you very much!! It worked here!!
Here's what worked in my test.
First, I've created 3 ratio measures: [A],[B], [C]:
A Ratio = DIVIDE(SUM('Table'[RES_ITEM_A]),COUNTROWS('Table'))
B Ratio = DIVIDE(SUM('Table'[RES_ITEM_B]),COUNTROWS('Table'))
C Ratio = DIVIDE(SUM('Table'[RES_ITEM_C]),COUNTROWS('Table'))
Now, I've created the 3 category measures:
Underdevelopd =
SUMX(
VALUES( 'Table'[Cd_aluno] ),
IF(
OR(
[A Ratio] >= 0.6,
AND(
[A Ratio] = [B Ratio],
[A Ratio] = 0.4
)
),
1,
0
)
)
Moderately Developed =
SUMX(
VALUES( 'Table'[Cd_aluno] ),
IF(
OR(
[B Ratio] >= 0.6,
AND(
[A Ratio] = [C Ratio],
[A Ratio] = 0.4
)
),
1,
0
)
)
Very Developed =
SUMX(
VALUES( 'Table'[Cd_aluno] ),
IF(
OR(
[C Ratio] >= 0.6,
AND(
[B Ratio] = [C Ratio],
[C Ratio] = 0.4
)
),
1,
0
)
)
And now you can toss them into a 100% stacked bar chart:
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |