Forum Discussion

Tiphany01's avatar
Tiphany01
Frequent Visitor
4 years ago
Solved

Matrix multiple measures display

Dear all,

I have the below data in excel and I would like to create a pivot in PowerBI as shown below. But I am unable to achieve the view I need. (Column Header interchange) The below is just an example :

 

Sample data:

AnimalsTypeLegsWeight
LionWild4200
TigerWild4220
DogDomestic415
CatDomestic45

 

Currently in PowerBI:

 

Desired View:

I have come across few posts suggesting unpivotting / calculation groups as a probable solution. But the dataset is of huge capacity and dont think both are feasable. Are there any other ways to achieve the desired view? 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Tiphany01 

    Thanks for reaching out to us.

    You can try this, create the table

    Table1 = GENERATE(
    VALUES('Table'[Animals]),
    {"Legs","Weight"})

    then create a measure

    Measure = 
    IF(MAX([Value])="Weight",
        CALCULATE(SUM('Table'[Weight]),FILTER('Table',[Type]=MAX([Type])&&[Animals]=MAX('Table1'[Animals]))),
        CALCULATE(SUM('Table'[Legs]),FILTER('Table',[Type]=MAX([Type])&&[Animals]=MAX('Table1'[Animals]))))

    result

     

     

    Best Regards,

    Community Support Team _Tang

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

2 Replies