Need Help: How to group by multiple conditions and calculate aggregated contribution

[unkuser]

Hi,

I'm here seeking for the wisdom of PowerBI masters.

I would like to plot a chart in PowerBI that could give the % contribution for a multiple conditional grouping.

To illustrate the issue, please find the table below:

Component	Project	Type	Area Used by Component	Total Area available for Project	Submiter
X	A1	NORMAL	10	30	FAB
Y	B1	EXTRA	12	40	IT
Z	C1	NORMAL	11	30	IT
K	D1	EXTRA	15	40	FAB
M	A1	NORMAL	17	30	IT
N	B1	EXTRA	10	40	FAB
O	C1	NORMAL	12	30	FAB
P	D1	EXTRA	13	40	IT

Based on a table like this, I would like to have the agreggated % contribution per Project Type and Submitter.

For the above example, if doing the calculations manually, we would have:

	NORMAL	EXTRA
FAB	22/60	25/80
IT	28/60	25/80
UNUSED AREA	10/60	30/80

The resulting chart should be like below:

However, I could not find a way in Power BI to dynamically perform this operation...

Any help or suggestion is very welcome.

Thanks!

[v-robertq-msft · Accepted solution]

Hi, @unkuser 

According to your description, I can roughly understand your requirement, I think your requirement can be separated into two parts, one part is the table and another is the column chart because the columns in the output table can’t be placed as the axis and value of the column chart, you can try my steps:

1. Create a calculated table:

Output =

SUMMARIZE('Table',[Submiter])

2. Create two calculated columns in the table like this:

NORMAL =

var _total=SUMX(FILTER(ALL('Table'),[Submiter]=EARLIER([Submiter])&&[Type]="Normal"),[Total Area available for Project])

var _area= SUMX(FILTER(ALL('Table'),[Submiter]=EARLIER([Submiter])&&[Type]="Normal"),[Area Used by Component])

return

DIVIDE(_area,_total)

EXTRA =

var _total=SUMX(FILTER(ALL('Table'),[Submiter]=EARLIER([Submiter])&&[Type]="Extra"),[Total Area available for Project])

var _area= SUMX(FILTER(ALL('Table'),[Submiter]=EARLIER([Submiter])&&[Type]="Extra"),[Area Used by Component])

return

DIVIDE(_area,_total)

This is the output:

3. To get the 100% stacked column chart, you should create a measure in the main table first:

Value =

DIVIDE(

    SUM('Table'[Area Used by Component]),

SUM('Table'[Total Area available for Project]))

4. Then create a 100% stacked column chart and place it like this:

Then change the data color, and you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Ashish_Mathur]

Hi,

Will there only be 2 submitter types - FAB and IT?

[unkuser]

Hi Ashish,

No. Number of submitters can grow up over time.

Thank you,

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[davehus]

Hi, Create measures as follows

FAB = CALCULATE(SUM(Matrix[Area Used by Component]), KEEPFILTERS(Matrix[Submiter]="FAB"))
IT = CALCULATE(SUM(Matrix[Area Used by Component]), KEEPFILTERS(Matrix[Submiter]="IT"))

Create a calculated column in your report as below:

Total By Type = Var MaxValues = CALCULATE(SUM([Total Area available for Project]), ALLEXCEPT(Matrix,Matrix[Type],Matrix[Submiter])) Return MaxValues

Create Measure

Unused Area = Var MaxV = MAX(Matrix[Total By Type]) RETURN MaxV-([FAB]+[IT])

Then build your chart as below:

 Hope this helps

[unkuser]

Hi davehus,

Thanks for the hints. It works fine for the types, but not for the Unused Area.

If we try to filter the chart by Project, the Unused Area will not be automatically updated to reflect the filters...

Any ideas ?

Thanks