Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
juniorcef
Frequent Visitor

Help to build chart from a category in Power BI

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.

juniorcef_0-1648941023770.png

 

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.

juniorcef_2-1648940365562.png

I don't know if I need to create an auxiliary table, could someone help?

1 ACCEPTED SOLUTION
rbriga
Impactful Individual
Impactful Individual

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:

Victory!Victory!

 

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

View solution in original post

2 REPLIES 2
juniorcef
Frequent Visitor

Thank you very much!! It worked here!!

rbriga
Impactful Individual
Impactful Individual

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:

Victory!Victory!

 

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors