Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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:
Output =
SUMMARIZE('Table',[Submiter])
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:
Value =
DIVIDE(
SUM('Table'[Area Used by Component]),
SUM('Table'[Total Area available for Project]))
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.
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:
Output =
SUMMARIZE('Table',[Submiter])
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:
Value =
DIVIDE(
SUM('Table'[Area Used by Component]),
SUM('Table'[Total Area available for Project]))
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.
Hi,
Will there only be 2 submitter types - FAB and IT?
Hi Ashish,
No. Number of submitters can grow up over time.
Thank you,
Hi,
You may download my PBI file from here.
Hope this helps.
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |