Forum Discussion
Calculated Column: % of Total
I'm trying to recreate an Excel dashboard in Power BI. Part of what I need to do is create a table or matrix (not sure which is best) that shows premium by segment, which I've done. Now I want to create another column that calculates the % of total premium for each segment. The end result should look something like this.
| Segment | Inforce Premium | Premium Distribution |
| A | $7,000,000 | 54% |
| B | $4,000,000 | 31% |
| C | $2,000,000 | 15% |
| Total | $13,000,000 | 100% |
In Excel, I can do this by referencing the cell with the premium for each segment and dividing by the total premium, like this:
| Column | B | C | D |
| Row | Segment | Inforce Premium | Premium Distribution |
| 3 | A | 7000000 | =C3/$C$6 |
| 4 | B | 4000000 | =C4/$C$6 |
| 5 | C | 2000000 | =C5/$C$6 |
| 6 | Total | =SUM(C3:C5) | =C6/$C$6 |
My problem is I can't figure out how to reference the column total for premium. Does anyone know how I could do this % of total in Power BI?
So you are saying that you don't want slicer to filter out the total data? In that case, you need to create calculated field.
For that you need to create measure, let's say it is called Total Sales,
Total Sales = CALCUALTE(SUM(Sales[Revenue]), ALL(Sales))
% Revenue = SUM(Sales[Revenue])/[Total Sales]
This is an idea, if you need further help, let me know.
4 Replies
Hi pdemontigny,
You can achieve by creating a new calculated column with following formula,
Premium Dist = DIVIDE(Question[Enforce Premium],SUM(Question[Enforce Premium])).
Let me know, if you have any queries.
There is inbuild feature in PowerBI to calculate the % of Grand Total.
Here is the link to more information https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-update-feature-summary/
Take a look at Analytic section
ANALYTICS
- Quick Calcs – % of grand total
I hope it is helpful.
Thanks,
P
Thanks for your help. This works, however, I want to add a slicer that allows the user to filter for a specific state - meaning the Inforce Premium volumes would change. Do you know how I could set up the formula so that Premium Dist shows the % of total premium for whatever subset of data the user has filtered for?
So you are saying that you don't want slicer to filter out the total data? In that case, you need to create calculated field.
For that you need to create measure, let's say it is called Total Sales,
Total Sales = CALCUALTE(SUM(Sales[Revenue]), ALL(Sales))
% Revenue = SUM(Sales[Revenue])/[Total Sales]
This is an idea, if you need further help, let me know.
