cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

percentages by grouping and using it as a filter to other pages in a Power BI

Hi,

I am new to Power BI, hope to find some help and guidance here.

 

I am trying to calculate percentages by different grouping dynamically. And that percentage should act as a filter/slicer to other visuals and page navigations. Below is a sample data that i have and percentages are shown here in the table are calculated based on the percentage of number of trainings assigned to an employee divided by number of trainings completed. It should aggregate based on if the reprot is based on Country or Employee etc. On the landing page, we just need to let the user enter a percentage value and on the button click it should go to different pages within the Report. So below is what I am trying to achieve:

 

1. Do we have a visual where user can enter a value? Or any workaround to achieve this?

2. The above value is nothing but a training percentage achieved, the landing page is aggregated at the highest level but still user should only enter 0 to 100 only. Can we restrict the user to these values?

3. Can a measure act as a slicer/filter to other pages? Once user enters a value, that value need to be passed on to other pages and detailed report is based on that value along with different groupings like by Employee, by Country etc.

4. How to achieve percentage calculation based on different groupings like at Employee level, Training type etc.? 

 

Employee NameEmployee IDCityCountryTrainings AssignedTrainings StatusTraining TypeTraining Completion %
A1ParisFranceT1CompletedPowerApps100%
A1ParisFranceT2CompletedPowerBI100%
B2LondonEnglandT1CompletedPowerApps33.33%
B2LondonEnglandT2PendingPowerBI33.33%
B2LondonEnglandT3In progressAutomate33.33%
C3DubaiUAET1PendingPowerBI0%
D4SidneyAustraliaT1In progressPowerBI0%

 

Looking forward to your suggestions, experts.

Thanks!

6 REPLIES 6
Anonymous
Not applicable

I am able to achieve percentage calculations at the detailed level but not able to get the slicer work because the percentage is a measure column. I am not able to get this measure converted to a Calculated column. Any ideas are highly appreciated. Please help me out with this.

I need to have a slicer with percentages so based on the percentage selected on my main page it will filter only those employees with that percentage values. 

Hi @Anonymous ,

 

I am very surprised that you can only use measure to achieve but not calculated column. Do you mind sharing your .pbix file?

 

Best regards,
Lionel Chen

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

 

v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

Maybe you can create a calculated column and add it to a slicer.

Column = 
VAR x= 
CALCULATE(
    COUNT(Sheet1[Trainings Assigned]),
    FILTER(
        Sheet1,
        Sheet1[Employee ID] = EARLIER(Sheet1[Employee ID]) && Sheet1[Trainings Status] = "Completed"
    )
)
VAR y = 
CALCULATE(
    COUNT(Sheet1[Trainings Assigned]),
    ALLEXCEPT(
        Sheet1,
        Sheet1[Employee ID]
    )
)
VAR z = 
DIVIDE(
    x, y
)
RETURN
IF(
    z = BLANK(),
    0,
    z
)

aaaa1.PNG

 

Best regards,
Lionel Chen

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

 

Anonymous
Not applicable

I tried but it is giving me only a count of trainings assigned in my case. I have various groupings in the reports, so on the landing page it's getting difficult to display the percentages in 0 to 100% range for the user to select. It is aggregating and displaying the percentages in 10K's. Any idea how to resolve this? I want to use same calculation for the slicer and the table display for each groupings in different tables in each of the pages.

amitchandak
Super User
Super User

@Anonymous ,

Try a measure like

divide(
calculate(count(table[Employee ID]),allexcept(Table,table[Employee ID]), Table[Trainings Status]= "Completed"),
calculate(count(table[Employee ID]),allexcept(Table,table[Employee ID]))
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Anonymous
Not applicable

Thanks Amit, for your response. Sorry, I think I didn't paste the correct the table and I didn't explain the scenario for percentage calculation properly. My employee number/ID is not all numbers it is a mix of numbers and text like below. And the percentage should per each employee(for all trainings assigned per completed) or per course type etc, so if an employee completes only 1 training from 3 trainings that he/she was assigned 33% will be the percentage populating for that employee.

 

Employee NameEmployee IDCityCountryTrainings AssignedTrainings StatusTraining TypeTraining Completion %
AAB1ParisFranceT1CompletedPowerApps100%
AAB1ParisFranceT2CompletedPowerBI100%
B2LondonEnglandT1CompletedPowerApps33.33%
B2LondonEnglandT2PendingPowerBI33.33%
B2LondonEnglandT3In progressAutomate33.33%
C3DubaiUAET1PendingPowerBI0%
DDESidneyAustraliaT1In progressPowerBI0%

 

And output should be:

Employee NameEmployee IDCity CountryTraining Completion %
AAB1ParisFrance100%
BAB1LondonEngland33.33%
C3DubaiUAE0%
DDESidneyAustralia0%

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!