Power BI tables with percentages

[JW1976]

Hello, i have a dataset which shows the status of various approved posts and the status of same (Whole Time Equivalents - WTEs) within an organisation under 5 Departments (AAA, BBB, CCC... as below)

My issue is that i am able to create a report table like above in Excel through Lookups, etc.  But i am having difficulty creating the same report in Power BI.  I am new to Power BI, but have a decent knowledge of Excel & Access.  My issue is on how to create 'measures'.

Basically the data behind this is an Excel file with a line for each post, showing whether it is 'Filled' (dark green columns above), 'Vacant' (light orange columns), and 'Filled (on Leave)' - which is a Sum Total of a lot of other categories of post (e.g. on Career Break, Maternity Leave, etc).

In Excel it is easy enough to create the lookups/formulas to pull in the right numbers for each field, but i can't seem to create the same in Power BI.  I think this is because the data which tells you whether a post is filled, vacant, etc is all in the same column.

Creating the Percentage columns based on that is also causing me difficulty, and the training manual i have doesn't seem to cover this type of report creation.

Any help would be greatly appreciated

[JW1976]

Thank you for your reply.

These are the main headings from the source (Excel file) which loads into Power BI.  Does this help?

Basically for each Agency in need to create a table showing a column with a sum total for the WTEs for each Agency (sum total of 3rd last column).  With the next column showing the Sum Total of 'Filled' posts, and a percentage column next to it.  Similarly i need a column showing the Sum Total of 'Vacant' posts with a percentage.  Then lastly i need a final column which sum totals up all of the WTE values where the 'Status of Post' (2nd last column) doesn't equal to Filled or Vacant, i.e. all other types of status of post like Sick Leave, Maternity Leave.

[v-huijiey-msft · Accepted solution]

Hi @JW1976 ,

Thanks for the reply from ahmedoye .

Please create 7 measures:

Calculate the total number of WTEs for each Agency:

ApprovedWTEs = SUM('Table'[WTE])

Calculate the total number of Filled for each institution:

FilledWTEs = CALCULATE(SUM('Table'[WTE]), 'Table'[Status of Post] = "Filled")

Calculate the total number of Vacant for each institution:

VacantWTEs = CALCULATE(SUM('Table'[WTE]), 'Table'[Status of Post] = "Vacant")

Calculate the total number of other statuses (not equal to Filled or Vacant) for each institution:

AllVacanies = CALCULATE(SUM('Table'[WTE]), NOT('Table'[Status of Post] IN {"Filled", "Vacant"}))

Calculate the percentage of Filled:

PercentFilled = DIVIDE([FilledWTEs], [ApprovedWTEs], 0)

Calculate the percentage of Vacant:

PercentVacant = DIVIDE([VacantWTEs], [ApprovedWTEs], 0)

Calculate the percentage of other statuses:

PercentOther = DIVIDE([AllVacanies], [ApprovedWTEs], 0)

Create a matrix visual object, drag Agencies into the rows, and drag the created measures into the values.

The final page visual is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[JW1976]

Hi Yang, thank you very much for this - it worked perfectly!

Just one other small question.  How do I change the format for the "% columns" from number to percentage?  I can't seem to find the area where I can do that

[v-huijiey-msft]

Hi @JW1976 ,

Glad to hear that your problem is resolved.

For your minor issue, please follow the steps below:

• Locate the measure in Power BI Desktop for which you need to change the format. for example, PercentFilled, PercentVacant, or PercentOther.
• Under the Measure tool pane, just select % and set the number of decimal places you want to keep.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

[ahmedoye]

Hi, it will help to share a sample of what your data set up in Power BI looks like.