Table Visual as per Slicer Selection

1
votes

I have a table visual that should have totals of employees count for each BRANCH( as per REGION selection in slicer), company total.

Requirement: If the region of North is selected in the slicer then the table visual should display the values for branches in the north region.

Problem: I am unable to get the branch wise values in the table visual as per the slicer.

I tried creating a measure for a branch and added it to the visual but when another region is selected, there is no data.

Please see screenshot below for more details: enter image description here enter image description here

Measures I have used:

Company Total = 
VAR _TotalAvg =
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( EmployeeCount[EmployeeID]),
            ALL (Branch[Region] ),
            ALLSELECTED ( 'Date'[Year] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( EmployeeCount[weeknum]),
        _TotalAvg,
        SUMX ( VALUES ( EmployeeCount[weeknum]), _TotalAvg )
    )

This below one to show how the values should be showing the table visual for all branches in the selected region.

Manang = CALCULATE (
    DISTINCTCOUNT ( EmployeeCount[EmployeeID]),
    FILTER ( branch, branch[Branch] = "Manang" )
)

Can anyone please let me know how I can achieve this?

Here is a reduced .pbix file on one drive. https://1drv.ms/u/s!Ave_-9o8DQVEgQ0MjEg21sSb38lT?e=pakuIP

Thanks

2
powerbidax

2 Answers

2
votes

It's not possible to dynamically change the measures shown in a Table visual based on a slicer selection.

Instead, consider using a Matrix visual, with a new measure based on DISTINCTCOUNT ( EmployeeCount[EmployeeID]), and then use BRANCH as a Column slicer in the visual.

Unfortunately, this technique would also repeat the [Company Total] measure for each branch, which is probably not what you want.

In general, though, for these "list"-type reports, consider using Power BI Paginated Report Builder, which lets you control to a much finer degree how data is displayed in rows and columns.

1
votes

There's no easy way to do this but it's technically possible.

If I had to, I'd approach it roughly as follows:

  1. Create a calculated table, slicer, to use for your region slicer and branch column using something like SUMMARIZE(branch, branch[Region], branch[branch]).

  2. Union that table with the cross-product of regions and measures. That is, regions {NORTH, SOUTH} and any measure names you want to use, {weeknum, DayOfTheWeek, CompanyTotal}.

CrossJoinTable =
CROSSJOIN (
    SELECTCOLUMNS ( { "North", "South" }, "Region", [Value] ),
    SELECTCOLUMNS ( { "weeknum", "DayOfTheWeek", "CompanyTotal" }, "Measure", [Value] )
)
  1. Create a switching measure that reads in the branch column of your slicer table (which includes the measure names now too) and returns the appropriate measure. Something like this:
    Switching Measure = 
    VAR branch = SELECTEDVALUE(slicer[branch])
    RETURN
        SWITCH ( branch,
            "weeknum", [weeknum measure],
            "DayOfTheWeek", [DayOfTheWeek measure],
            "Company Total", [Company Total],
            CALCULATE(
                DISTINCTCOUNT ( EmployeeCount[EmployeeID] ),
                branch[Branch] = branch
            )
        )

In your matrix, put StartDate in the rows, slicer[branch] in the columns, and [Switching Measure] in the values.

As @Dan points out, there may be better tools for the job but sometimes a workaround is possible if you really need it.