Forum Discussion

krishnavzm123's avatar
krishnavzm123
Regular Visitor
3 years ago
Solved

How to transform the excel input into a summary table

Hi Team,

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

Class                              Colour              Count

Class1Orange4
Class1Orange6
Class1Red5
Class2Blue4
Class2Yellow6
Class2Blue2

 

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

Class1Orange5
Class1Red5
Class2Blue3
Class2Yellow6
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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