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 Class1 Orange 4 Class1 Orange 6 Class1 Red 5 Class2 ...
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

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