How to transform the excel input into a summary table

[krishnavzm123]

Hi Team,

I'm new to Power BI and getting input in below format.

Class                              Colour              Count

Class1	Orange	4
Class1	Orange	6
Class1	Red	5
Class2	Blue	4
Class2	Yellow	6
Class2	Blue	2

from the above I'm planning to generate a new table but not sure how can i achieve. Can you guide me how can i generate a summary table like below ,

and also

if i need to use Mesaure can you tell me how i can compare the class , colour to get the abg count?

Class                     Colour      Avg count

Class1	Orange	5
Class1	Red	5
Class2	Blue	3
Class2	Yellow	6

[Anonymous · Accepted solution]

Hi @krishnavzm123,

It sounds like a common measure expression calculate with multiple aggregates requirements.

If that is the case, you can create a measure formula with summarize function to aggregate and use iterator function AVERAGEX get the average of count of records.

formual =
AVERAGEX (
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [Class],
        [Colour],
        "cColor", COUNTA ( Table[Colour] )
    ),
    [cColor]
)

Regards,

Xiaoxin Sheng