Forum Discussion

CadenChow's avatar
CadenChow
Frequent Visitor
5 years ago
Solved

How can I create summary table with strange requirement?

Hi all,   I am new joiner of this forum nice to meet you😃   I am creating power BI dashboard but I have no clue of how to create the summary table with information below:   Full list of RAG st...
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi CadenChow ,

     

    According to your needs, I did the following test. First, I created a measure to count the RAG value corresponding to the maximum value in the Item field under the Group grouping. Then create a virtual table, get the correct result, refer to the following: 

    M = 
VAR _maxitem =
    CALCULATE ( MAX ( 'Table'[Item] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[RAG] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Group] = SELECTEDVALUE ( 'Table'[Group] )
                && 'Table'[Item] = _maxitem
        )
    )
    Result_ = SUMMARIZE('Table','Table'[Group],"RAG",[M])


    If the problem is still not resolved, please provide detailed error information. Let me know immediately, looking forward to your reply.

     

    Here is the sample pbix file.

    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

Hey, @CadenChow

Can you explain yourself a little more?

Because when I see group C, one element has amber, and another element has red, but the result is only red.

Perhaps the requirement is to show the result of the highest item number in each group?

Please help to understand more.

Thank you.

Hi, my name is Jihwan Kim.

If this post helps, consider accepting it as the solution to help other members find it faster.

CadenChow's avatar
CadenChow
Frequent Visitor
5 years ago

Thanks for your reply. 

 

Perhaps the requirement is to show the result of the highest item number in each group? <- Yes. I want to show highest rating of the group only. I am thinking shall I need to convert the RAG to number then use Max function.