Forum Discussion

arjun17697's avatar
arjun17697
New Member
1 year ago

Stacked Chart with labels

I have written a DAX code below:

 

 

 

 

Ranking = 
VAR _Top_Concept=RANKX(ALL(Dim_Concept[Concept Cleaned]),[EmployeeCount],,DESC)
VAR _bottom_Concept=RANKX(ALL(Dim_Concept[Concept Cleaned]),[EmployeeCount],,ASC)

VAR _Top_Territory=RANKX(ALL(Dim_Territory[Territory]),[EmployeeCount],,DESC)
VAR _bottom_Territory=RANKX(ALL(Dim_Territory[Territory]),[EmployeeCount],,ASC)

VAR _Top_nationality=RANKX(ALL(Dim_Nationality[NATIONALITY]),[EmployeeCount],,DESC)
VAR _bottom_nationality=RANKX(ALL(Dim_Nationality[NATIONALITY]),[EmployeeCount],,ASC)

VAR _Top_FUNCTION=RANKX(ALL(Dim_Function[Function]),[EmployeeCount],,DESC)
VAR _bottom_FUNCTION=RANKX(ALL(Dim_Function[Function]),[EmployeeCount],,ASC)

VAR _Top_band=RANKX(ALL(Dim_Band[BAND]),[EmployeeCount],,DESC)
VAR _bottom_band=RANKX(ALL(Dim_Band[BAND]),[EmployeeCount],,ASC)

VAR _CheckRank=
if(CONTAINSSTRING(SELECTEDVALUE('Select ToRank'[Select ToRank Fields]),"Territory"),
    IF(SELECTEDVALUE('Choose RankType'[Select])="Top", _Top_Territory,_bottom_Territory
    ),
    if(CONTAINSSTRING(SELECTEDVALUE('Select ToRank'[Select ToRank Fields]),"Concept"),
        IF(SELECTEDVALUE('Choose RankType'[Select])="Top", _Top_Concept,_bottom_Concept
        ),
        if(CONTAINSSTRING(SELECTEDVALUE('Select ToRank'[Select ToRank Fields]),"Function"),
            IF(SELECTEDVALUE('Choose RankType'[Select])="Top", _Top_FUNCTION,_bottom_FUNCTION
            ),
            if(CONTAINSSTRING(SELECTEDVALUE('Select ToRank'[Select ToRank Fields]),"Band"),
                IF(SELECTEDVALUE('Choose RankType'[Select])="Top", _Top_band,_bottom_band
                ),
                if(CONTAINSSTRING(SELECTEDVALUE('Select ToRank'[Select ToRank Fields]),"NATIONALITY"),
                    IF(SELECTEDVALUE('Choose RankType'[Select])="Top", _Top_nationality,_bottom_nationality
                    )
                )
            )
        )
    )
)

RETURN 
if(_CheckRank<='Choose Rank'[Choose Rank Value],[EmployeeCount])

 

 

 

 

This code takes three parameters:
Select to Rank: chooses which parameter to show
Choose Rank: How many values in parameter to be shows
Choose Rank Type: Whether user want to see top rankers or bottom rankers

Ranking is based on employee count


I'm creating a 100% stacked chart where:

- The X-axis displays the selected parameter from "Select to Rank."
- The Y-axis uses the Ranking measure.
- The legend shows experience groups (employees grouped by work experience).

When I select the top 2 ranks for the "functions" parameter, Power BI currently gives top ranks for each experience group separately, showing multiple columns.

What I need is for Power BI to show only two columns—one for each of the top two functions based on overall employee count—and then stack these columns by experience groups within each function.

Need Help

2 Replies

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    1 year ago

    Hi arjun17697 

    You’ll need to filter your data so that only the top two overall "Function" values based on employee count are displayed across experience groups. Please find the adjustment to your DAX measure to rank by employee count and create a filtered measure for visualization.

     

    Create a calculated table to identify the top functions based on total employee count across all experience groups.

    Top2Functions = 
TOPN(
    2,
    SUMMARIZE(
        Dim_Function,
        Dim_Function[Function],
        "TotalEmployeeCount", SUM(Fact_Employee[EmployeeCount])
    ),
    [TotalEmployeeCount],
    DESC
)

    Update your ranking measure to filter only the functions in this top two list and stack them by experience group.

    Ranking = 
VAR _SelectedFunction = SELECTEDVALUE(Dim_Function[Function])
VAR _IsTop2 = CALCULATE(
    COUNTROWS('Top2Functions'),
    'Top2Functions'[Function] = _SelectedFunction
)

RETURN
IF(
    _IsTop2 > 0,
    [EmployeeCount],
    BLANK()
)