Visualising a count of one column as a percentage of another

[TMort]

Hi,

I have two tables, both with columns [Division] and [Staff Number]

One is a list of all staff present in a particular month, the other is a list of those staff which moved role that month.

I want to visualise, for each divison, the number of people who move in that divison, as a percentage of the number of staff that were present for that division.

I have done this manually in excel, but percentages come out differently in BI and I can't figure out why.

I've tried with a one-to-many relationship on [Staff Numbner] (because some people moved more than once) and a many-to-many relationship on Division, both calculations come out incorrect.

I've also tried excplicitly stating the relationship inside DAX like this:

Mobility_Percentage =

DIVIDE(

    COUNTROWS('mobility career development'),

    CALCULATE(

        COUNTROWS('Cumaltive HeadCount May'),

        TREATAS(VALUES('mobility career development'[Division]), 'Cumltive HeadCount May'[Division])

    ),

    0

)

Any thoughts welcome!

[MasonMA · Accepted solution]

@TMort 

If in your situation, i would create one Dimension table with all distinct 'Divisions' and use it to filter the other two tables. 

You can create this table either in Power Query or through DAX, if by DAX use below code format

Division =
DISTINCT(
UNION (
    SELECTCOLUMNS('table1', "Division", 'table1'[Division]),
    SELECTCOLUMNS('table12', "Division", 'table2'[Division])
)
)

Build the relationship, and then update your measure:

Mobility_Percentage = 
DIVIDE(
    CALCULATE(COUNTROWS('table1')),
    CALCULATE(COUNTROWS('table2')),
    0
)

[v-pnaroju-msft]

Hi TMort,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution, as this helps the broader community.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi TMort,

We would like to know if the details we shared have helped solve your problem. If you require any additional assistance, feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi TMort,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @MasonMA, @ajaybabuinturi, and @Daniel29195, for your responses.

Hi TMort,

We appreciate your question on the Microsoft Fabric Community Forum.

To calculate the mobility percentage correctly, please create a shared Division dimension table and connect it to both the Mobility and Headcount tables. This will help in filtering the data properly and avoid confusion caused by many-to-many relationships or multiple moves.

Kindly find attached the screenshot and a sample PBIX file for your reference:

We hope this information helps solve your issue. If you have any more questions, please feel free to ask the Microsoft Fabric community.

Thank you.

[Daniel29195]

Hello @TMort

Hope you are doing great. 

i will try to help you out with the issue .

since you want to work on the division level,  i would suggest to create a table called dimdivisions. this table will have all the divisions from both tables union together. 

1. you can use this DAX code to create such table. 

dimdivision =  disitinct ( union ( allnoblank(table_1[division]) , allnoblank(table_2division]) ) ) 

2.step 2 

now that you have create this table,  you can link this table to both tables as a 1 - to - many relationshiop/

final step : 

Mobility_Percentage =

DIVIDE(

    COUNTROWS('mobility career development'),

        COUNTROWS('Cumaltive HeadCount May')

    ,

    0

)

hope that helps . 

[ajaybabuinturi]

Hi @TMort,

Wanted to let you know that the provide DAX logic will work, if you’re using the Division from mobility career development in your visual.
However I have other workaround please try that and let me know how it works.
1.Create a bridge table for Division

Division Lookup = DISTINCT(UNION(
    SELECTCOLUMNS('mobility career development', "Division", 'mobility career development'[Division]),
    SELECTCOLUMNS('Cumltive HeadCount May', "Division", 'Cumltive HeadCount May'[Division])
))

2.Create one to many relationships b/w mobility career development'[Division](*)<---(1)Division Lookup Cumltive(1)-->(*)HeadCount May'[Division]
3.Create below DAX logic

Mobility_Percentage =
VAR CurrentDivision = SELECTEDVALUE('Division Lookup'[Division])

VAR Movers = CALCULATE(COUNTROWS('mobility career development'),
        'mobility career development'[Division] = CurrentDivision)

VAR TotalStaff = CALCULATE(COUNTROWS('Cumltive HeadCount May'),
        'Cumltive HeadCount May'[Division] = CurrentDivision)

VAR Result = DIVIDE(Movers, TotalStaff, 0)

RETURN
Result

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[MasonMA · Accepted solution]

@TMort 

If in your situation, i would create one Dimension table with all distinct 'Divisions' and use it to filter the other two tables. 

You can create this table either in Power Query or through DAX, if by DAX use below code format

Division =
DISTINCT(
UNION (
    SELECTCOLUMNS('table1', "Division", 'table1'[Division]),
    SELECTCOLUMNS('table12', "Division", 'table2'[Division])
)
)

Build the relationship, and then update your measure:

Mobility_Percentage = 
DIVIDE(
    CALCULATE(COUNTROWS('table1')),
    CALCULATE(COUNTROWS('table2')),
    0
)