Replicate Excel Pivot in Power BI

[Anonymous]

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=10380...

Thanks.

PG

[Anonymous]

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!

[Anonymous]

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.

[amitchandak]

@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])