How can I create summary table with strange requirement?

[CadenChow]

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 status

Group	Item	RAG
A	1	Green
A	2	Green
A	3	Green
B	1	Green
B	2	Amber
C	1	Green
C	2	Amber
C	3	Red

Summary (if any item in Group = Amber then result is Amber, if any times have red then the result of group is Red). May I know how can I make it? Thanks.

Group	RAG
A	Green
B	Amber
C	Red

[v-henryk-mstf · Accepted solution]

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]

Hey, @CadenChow

Please try the following measure.

RAG by the highest item number

Was

maxitemnumber = MAX('Table'[Item])

Return

CALCULATE( SELECTEDVALUE('Table'[RAG]), 'Table'[Item] = maxitemnumber)

Hi, my name is Jihwan Kim.

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

[Jihwan_Kim]

Hi, @CadenChow 

Please try the below measure.

RAG by highest item-number =

VAR

maxitemnumber = MAX('Table'[Item])

RETURN

CALCULATE( SELECTEDVALUE('Table'[RAG]), 'Table'[Item] = maxitemnumber)

Hi, My name is Jihwan Kim.

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

[CadenChow]

Thanks. May I know why we need to add selectedvalue in the calculate?

[Jihwan_Kim]

Hi, @CadenChow 

Thank you for your feedback.

I used selectedvalue because it had to select the color from the group selection. Then, I could find the maximum number of the item.

Thank you.

Hi, My name is Jihwan Kim.

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

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.

[Syndicate_Admin]

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]

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.

[Jihwan_Kim]

Hi, @CadenChow 

Can you please explain a bit more?

Because when I see group C, one item has Amber, and another item has Red, but the result is only Red.

Perhaps, is the requirement is to show the result that has the highest item number in each group?

Please help to understand more.

Thank you.

Hi, My name is Jihwan Kim.

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