Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Replicate Excel Pivot in Power BI

Hello All!

 

I have this Excel pivot with filters and would like to replicate this in Power BI.  

 

Note that this changes based on selected filters, where Total ID = TPID Count, Actual = MEI Rank Actual and % = MEI Rank Actual over Total ID,

 

I'm struggling replicating in Power BI.  Anyone can help?  I have attached the excel file.

 

https://docs.google.com/spreadsheets/d/1SNSiCRzlLndX6j9za4ww15vIWUYFjEvc/edit?usp=sharing&ouid=103803004788144269422&rtpof=true&sd=true

 

Thanks.

PG

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can create a matrix Visual

     

    Subsidiary on Row, Qtr on the column

    And following values

    Measures

    Actual = Sum(Table[MEI Rank Actual])  // use count if needed, place of sum

    Total ID = Count(Table[TPID])

     

    % = divide([Actual], [Total ID])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you amitchandak!  I have it created it, but getting this result.

     

    Where Total TPID in each segment group should be 591, not the total of 591*9(5319) for enterprise.  Same goes with other segment groups.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I was able to replicate the results of the source data from the cube, and it shows the following breakdown.

    This is the expected output,but something is wrong when I put another level of drilldown to it, where 

    INCORRECT as this one sould have still 591 in the Enterprise segment group instead of 5319. Same goes with other segment group.

     

    It looks good in the cube's excel pivot, though.

     

     

    Thanks for your ideas amitchandak .  Appreciate it!