i have a problem statement with a data being like this,
1ST COLUMN – grp(group)
2nd col – dept(department)
3rd col – group_sales(in %)
4th col – dept_sales(in %)
enter image description here
The source data has already been aggregated at group and department level,as such where 3rd col would display the sales% for group CAR(and that y its the same across all rows), and the 4th column gives the sales% of each dept.
Just to give the context, the departments come under the group in hierarchy.
I need to create a matrix visual because i need the drill down effect for the hierarchies (under group CAR, we have 4 dept – 34,70,72,81) and display the % of sales. I did some preprocessing and brought the sales for each group and dept under one column
enter image description here
When i am creating the matrix visual, i am getting something like this
enter image description here
Instead of getting 0.58 for car, m getting 0.49. Basically power bi is doing an average of the sales% of each dept and populating that value for Car which is at a higher level of hierarchy to department, BUT what I need is the power bi matrix visual to give me 0.58 for Car, basically displaying the data as it. So can any one help on how to achieve this. (please note i have achieved the drill down effect by creating multiple dimension table and connecting that to the fact table which looks like (https://i.stack.imgur.com/JgjPO.png)