Drilldown and Groupby - individual total divided by group total

[javedbh]

Here is the data I have

MainCategory |	SubCategory |	Device |	Lot |	SubLot |	Bin |	PartCount
Cat1	SubCat1	Dev1	Lot1	Sub0	30	9
Cat1	SubCat1	Dev1	Lot1	Sub0	31	18
Cat1	SubCat1	Dev1	Lot1	Sub0	32	15
Cat1	SubCat1	Dev1	Lot1	Sub0	45	11
Cat1	SubCat1	Dev1	Lot1	Sub1	30	7
Cat1	SubCat1	Dev1	Lot1	Sub1	31	4
Cat1	SubCat1	Dev1	Lot1	Sub1	32	23
Cat1	SubCat1	Dev1	Lot1	Sub1	50	16
Cat1	SubCat1	Dev1	Lot1	Sub1	52	8
Cat1	SubCat1	Dev1	Lot2	Sub0	30	15
Cat1	SubCat1	Dev1	Lot2	Sub0	31	17
Cat1	SubCat1	Dev1	Lot2	Sub0	32	11
Cat1	SubCat1	Dev1	Lot2	Sub0	45	18
Cat1	SubCat1	Dev1	Lot2	Sub1	30	9
Cat1	SubCat1	Dev1	Lot2	Sub1	31	5
Cat1	SubCat1	Dev1	Lot2	Sub1	32	17
Cat1	SubCat1	Dev1	Lot2	Sub1	50	11
Cat1	SubCat1	Dev1	Lot2	Sub1	52	3
Cat1	SubCat1	Dev2	Lot3	Sub0	30	9
Cat1	SubCat1	Dev2	Lot3	Sub0	31	18
Cat1	SubCat1	Dev2	Lot3	Sub0	32	15
Cat1	SubCat1	Dev2	Lot3	Sub1	30	7
Cat1	SubCat1	Dev2	Lot3	Sub1	31	4
Cat1	SubCat1	Dev2	Lot3	Sub1	32	23
Cat1	SubCat1	Dev2	Lot4	Sub0	30	15
Cat1	SubCat1	Dev2	Lot4	Sub0	31	17
Cat1	SubCat1	Dev2	Lot4	Sub0	32	11
Cat1	SubCat1	Dev2	Lot4	Sub0	45	18
Cat1	SubCat1	Dev2	Lot4	Sub1	30	9
Cat1	SubCat1	Dev2	Lot4	Sub1	31	5
Cat1	SubCat1	Dev2	Lot4	Sub1	32	17
Cat1	SubCat1	Dev2	Lot4	Sub1	50	11

I want to create a bar chart with multiple drill down.

Following columns are added in the x-Axis for multiple drill down:

MainCategory, SubCategory, Device, Lot, SubLot

Column "Bin" is added under Legend.

Note: Drill Mode is on - select a data point to drill

Note: User is not interested in all bins, only some of the bins.

I want to display percentage of individual bin at whatever the level (drill down/group by) the user is at. Column "PartCount" will be used to get count of individual bin and also to get count of all bins at selected level.

E.g. user has drilled down like Cat1->SubCat1->Dev1->Lot1. Now Sub0 and Sub1 are displaying. User want to see percentage of bin 30, 45, 50. Total PartCount is 53 in Sub0 and 58 in Sub1. The data should be something like this:

Group by SubLot
MainCategory	SubCategory	Device	Lot	SubLot	Bin	PartCount	Total By SubLot	Percentage
Cat1	SubCat1	Dev1	Lot1	Sub0	30	9	53	9/53*100=16.9811321
Cat1	SubCat1	Dev1	Lot1	Sub0	45	11	53	11/53*100=20.754717
Cat1	SubCat1	Dev1	Lot1	Sub1	30	7	58	7/58*100=12.0689655
Cat1	SubCat1	Dev1	Lot1	Sub1	50	16	58	16/58*100=27.5862069

If the user is at Lot level (Cat1->SubCat1->Dev1):

Total PartCount in Lot1 = 111

Total PartCount in Lot2 = 106

Group by Lot
MainCategory	SubCategory	Device	Lot	Bin	PartCount	Total By Lot	Percentage
Cat1	SubCat1	Dev1	Lot1	30	16	111	14.41441441
Cat1	SubCat1	Dev1	Lot1	45	11	111	9.90990991
Cat1	SubCat1	Dev1	Lot1	50	16	111	14.41441441
Cat1	SubCat1	Dev1	Lot2	30	24	106	22.64150943
Cat1	SubCat1	Dev1	Lot2	45	18	106	16.98113208
Cat1	SubCat1	Dev1	Lot2	50	11	106	10.37735849

Please tell me how I can achieve this measure!!!

Any help would be greatly appreciated.

[Sean · Accepted solution]

@javedbh  Did you try the column and line chart?

Organize as in the picture...

[Sean]

@javedbh Yes you can do this with a Line and Column chart.

But the % will show 100% up until you get to the final drill down level.

Why don't you use a Matix.

[javedbh]

@Sean Can you tell me how do you calculate "% of Lot" column?

Also is it possible to show %age like the following format:

Thanks

[ankitpatira]

@javedbh I have played with your scenario for a while now. I created sample dataset as yours however it doesn't turn out to be easy to do in PBI as what you want is conditional totalling based on the level user navigates to.

What I would suggest is instead of having one bar chart create multiple bar charts for each level that way it would be easy enough to do just in power bi. Let me know if you want to take this approach and I can give you list of steps to follow.

Other option is to use R in power bi which is very powerful to do these  type of transformation but for that you need to know R. Again let me know if you wan't to take R approach.

[javedbh]

@ankitpatira I did tried multiple charts option. I used one bar chart and one line chart. Bar chart is used for levels and line chart for bins and bin percentage. Selecting one datapoint in bar would display percentage of bins of the selected datapoint in the line chart. But it is not a perfect solution.

I would be interested in your proposed R solution. Can you help me with that?

Thanks

[Sean · Accepted solution]

@javedbh  Did you try the column and line chart?

Organize as in the picture...

[javedbh · Accepted solution]

@Sean The query you added in your reply solved the problem. Thanks.

Here is the result:

[javedbh]

@Sean I will definitely try this and thanks for puthing the formula for the % measure.

[javedbh]

I want something like one of the following (created in excel):

[Sean]

@javedbh this is not possible in PBI but there's an idea here if you want to Vote for Nested X-Axis (I've already voted for it)

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11524623-nested-x-axis-labels

[javedbh]

@Sean Nested X Axis are definitely good but for the time being I'm not interested in them for the problem I posted. I just wanted to show that if there are e.g. multiple x-axis items (e.g. Lot1, Lot2, Lot3, Lot4 on x-axis) then each item should have multiple bars (one for each bin).