Issue with Calculating Dynamic Percentage in Power BI

[ginalu]

I need to create a dynamic measure in Power BI that calculates the percentage of a specific column (TeacherCount) within each cohort. This percentage should be displayed on the Y-axis of a chart, with the X-axis showing different cohorts.

Requirements:

X-Axis: Cohort
Y-Axis: Percentage of TeacherCount for various dimensions (e.g., Country) within each cohort.

Challenge:

I need a single dynamic formula that adjusts automatically based on the selected dimension in the visual, rather than creating separate formulas for each dimension.
The percentage should be calculated by summing the TeacherCount column for the relevant dimension and cohort.

Current Formula Attempt:

DAX
TeacherPercentage = VAR TotalTeacherCountInCohort = CALCULATE( SUM(UserSummaryTable[TeacherCount]), ALLEXCEPT(UserSummaryTable, UserSummaryTable[Cohort]) ) VAR CurrentTeacherCount = SUM(UserSummaryTable[TeacherCount]) RETURN DIVIDE(CurrentTeacherCount, TotalTeacherCountInCohort, 0) * 100

Request:

I seek guidance on how to adjust or improve this formula to correctly calculate the percentage of TeacherCount across different dimensions dynamically. Alternatively, if there are simpler or faster methods to calculate column percentages, please advise.

[lkalawski · Accepted solution]

Hi @ginalu ,

I hope I understood your problem correctly.

You have several dimensions in your model and a fact table that is connected to them. You want to calculate the share per dimension depending on which dimension is selected.

You don't want to write separate measures for each dimension, but you want to have it in one measure.

It is doable.

Proposed solution:
1. In my case, I created a model that consists of 3 tables (2 dimensions and one fact). I have information about the number of products sold per Country and per Product Group.

2. I created a Field Parameter that allows you to very efficiently change the dimension you want to analyze. I added a slicer to the visualization to switch the dimension and set it to single selection.

I have added an additional column: Dimension Name to the field parameter, which will allow you to easily check what exactly has been selected:

3. I wrote a Share measure that first checks the selected dimension. Then, for each dimension, Icalculate its total separately. In result, I check what was selected, and then calculate Share.

Share = 
VAR __SelectedDimension = SELECTEDVALUE('Dimension'[Dimension Name])
VAR __ProductGroup = CALCULATE([Sum of Value], ALL('Product Group'[Product Group]))
VAR __Country = CALCULATE([Sum of Value], ALL(Country[Country]))

VAR __Result = 
    SWITCH(__SelectedDimension
        , "Product Group", DIVIDE( [Sum of Value], __ProductGroup)
        , "Country", DIVIDE( [Sum of Value], __Country)
    )

RETURN 
    __Result

4. The result:

a. 

b. 

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin