using slicer output to match and calculate specific data from a table

[J_Eastwood]

Aim:

to be able to use a slicer in PBI to select a region, which then filters and calculates averages from only specific rows of a datatable (specific to the region selected), we use the term "statistical neighbours". Determining the statistical neighbour comparison data is what I need support with as there are around 150 regions and each region has a unique list of 10 regions that are in it's statistical neighbour comparison group. 

We have a current Excel process that uses a dropdown filter to select a region from a list. There is also an excel tab with a matrix type table, which has all regions listed along a row and also down a column, with "Yes" added to any cell whereby the column region is a statistical neighbour match for the row region (see below, the matching is actually done on the region codes, such as 301, rather than the names):

The important thing to note is that there are not 15 groups of 10 regions, each region has its own list of 10 statistical neighbours, so while there is some crossover between groups, they are all different.

on a seperate tab, the data required is stored, with a column for each required data item and an additional column that notes whether the regions in each row are a statistical neighbour for the region selected from the dropdown:

the formula for this is =INDEX(StatNeighboursTab![data range],1,MATCH([Region Code],StatNeighboursTab![Region code row],0))

the report then has a row underneath all of the data columns to pull back the average of the data above, but only if the Statistical neighbour column brings back a "Yes".

the formula for this is =IFERROR(AVERAGEIF($A24:$A180,"Yes",K24:K180),"-")

where A is the statistical neighbour column and K is the data column in this case, but there are lots of other columns that require the same process.

Is there a way to replicate this? I can't see how you could do the same matching in power BI due to the table structure, but there may be a better way to achieve the same thing using different methods. Ideally by selecting the region in the slicer, each data column will have the statistical neighbour value calculated in one go, but if each data column needs a specific calculated column / measure, then that is an option, as there are only around 20-30 different major data columns to work out. 

any thoughts appreciated as i cannot get my head around how to start this in PBI  

[Anonymous]

Hi @J_Eastwood ,

Based on your problems, I think you can achieve similar functionality by leveraging DAX and Power Query.

You can connect to your Excel files and use Power Query.

Then you can create a relationship between the region table (with the list of regions) and the data table (with the required data items). Define a calculated column in the region table to determine whether a region is a statistical neighbor based on the region codes.

IsStatisticalNeighbor = 
    IF(
        COUNTROWS(
            FILTER(
                StatNeighboursTab,
                StatNeighboursTab[Region Code] IN VALUES(RegionTable[Region Code])
            )
        ) > 0,
        "Yes",
        "No"
    )

Create a slicer in your report to allow users to select a region. Use the selected region to filter the data table based on the IsStatisticalNeighbor column.

FilteredData = 
    FILTER(
        Data,
        Data[Region Code] IN
            CALCULATETABLE(
                RegionTable,
                RegionTable[IsStatisticalNeighbor] = "Yes"
            )
    )

Create measures for each required data item (e.g., K) using DAX functions.

AverageK = 
    IFERROR(
        AVERAGEX(
            FILTER(
                FilteredData,
                FilteredData[Statistical Neighbor] = "Yes"
            ),
            FilteredData[K]
        ),
        "-"
    )

Finally, you can add a card visual or other appropriate visual to display the calculated average for each data item.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

How to Get Your Question Answered Quickly 

Best Regards

Yilong Zhou

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

[J_Eastwood]

@Anonymous I think the basics of what you're suggesting are right, but I think i just need to clarify how the data is structured currently and especially the statistical neighbour table.

For clarity, the geographical regions I am working with are called LAs, so i have used that naming below.

regarding the response above, you have 3 datasets:

• the regional table which has the list of LAs and codes (ID), i have called mine LA List
• the statistical neighbour table, i have called Stat Neighbours
• the data table, i have called Data Table

I think the main issue is that currently the Stat Neighbour table, as it exists in Excel, is not in a format Power BI can understand, as it is not a proper table as it uses both the row and the column context to match the LAs up:

one thing i missed before was that then below this table is a row that shows the selected LA, e.g. Birmingham, and the row then returns "Yes" for all the columns where the selected LA has a "Yes". It is this row that the statistical neighbour column on the data table (in Excel) is matching up to. 

I have created a table with the same information as the stat neighbours sheet (removing the LA names),

however it means that your IsStatisticalNeighbour calculated column will just return "Yes" for all rows in the LA List table, as they both list all of the LA IDs.

also, if I create the FilteredData measure above, i get the following error

so not sure if it wants me to reference the other columns in the table.

I don't know if it would be easier to match using the codes instead of "Yes" like this:

or even removing all the blanks in each column and then transposing the data so you have stat neighbour1, 2, 3, etc.?

I can't add my excel files, but hopefully that makes sense.

So in the example above, if LA ID 301 was selected, for columnK for example, i would get back an average of column K, only where the LA ID was 308, 203, and 316.

If 302 was selected, i would get back the average of K for LA IDs 202, 201, 307, 310, 312, 313, 207, 314, 315, 870, 317, 871, 319, 212, and 213.

thank you for the effort so far