Show other items in a table visual on a page filtered for 1 item only

[Anonymous]

Hello,

I have drill-trough cross reporting set up.

It works between Brand and SKU pages.

So a user is able to click on a specific SKU on the Brand page and it would forward to the SKU page.

The SKU page as a result is automatically filtered on the only one selected item.

The problem is that on this SKU page I need to add a table with all the SKUs within same category for comparison.

Any ideas?

[v-yingjl]

Hi @Anonymous ,

Sample data or file and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Yingjie Li

[Anonymous]

Hello @v-yingjl ,

I am sorry.

I created a sample dataset and a report.

Please follow the link

Thank you!

[v-yingjl]

Hi @Anonymous ,

It seems like I have no access to the link, it requires to sign-in and needs visit access to get the sample file. Perhaps you can consider re-sharing the sample file. Or if the sample file is very easy and it is inconvenient for you to share it, you can share some scrrenshots about the dataset and reports here and I could try to re-bulid it in my environment to help you better.

Best Regards,
Yingjie Li

[Anonymous]

That's strange. I might forgot to remove all restrictions.

Please try again.
link

I've changed it in the previous comment as well.

Thank you!

[v-yingjl]

Hi @Anonymous ,

Based on the sample file, you want to get a table with the TOP5 SKUs in the same category

Maybe this is your expected output table:

You can create this measure, put it in the visual filter and set its value <=5

Measure =
VAR tab =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                ALL ( SKU ),
                'SKU'[Category] IN DISTINCT ( 'Category'[Category ID ] )
                    && 'SKU'[SKU] IN DISTINCT ( 'Sales'[SKU] )
            ),
            'SKU'[SKU name],
            'Category'[Category Name],
            "New Sales", SUM ( 'Sales'[Sales] )
        ),
        ALL ()
    )
RETURN
    RANKX (
        FILTER ( tab, [Category Name] IN DISTINCT ( 'Category'[Category Name] ) ),
        [New Sales],
        SUM ( 'Sales'[Sales] ),
        DESC,
        DENSE
    )

Attached the sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[Anonymous]

Hi @v-yingjl,

Unfortunately the solution doesn't fully cover the requirements.

Once I drill-through from the previou page on any SKU it show me only one SKU in the table.

Could you check?

[v-yingjl]

Hi @Anonymous ,

If wants to combine topN with drilthrough, I'm afraid it could not support in power bi currently because when report consumers choose to drill through, the page is filtered to show information about the data point on which they right-clicked. 

For example, in the sample file, when right-click on a data point about 1st Snack, a SKU page, and select to drillthrough. The drillthrough page they go to is fitered to 1st Snack, so it will just return the corresponding result in the dirllthrough page, ohter values will be filtered. In ohter words, topN and drillthrough could be conflicted.

Best Regards,
Yingjie Li

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

[Anonymous]

I guess I found a solution for this, it is absolutely not resource-efficient but it does the job.

Maybe someone is interested.

If we can't do this with a measure, I duplicated DIM tables so they are not connected to the current filter and try to replicate it there?

Please file an updated file

There is a measure that managed to show me sales by SKU and by category that I can easily narrow down to TOP 5 with a filter:

Sales by category = CALCULATE(sum(Sales[Sales]),filter(all(Sales),Sales[SKU]=SELECTEDVALUE('Second SKU'[SKU])), filter('Second Category', 'Second Category'[Category ID ] = SELECTEDVALUE(SKU[Category])))

I tried to repeat the same on my original dataset and it still doesn't work, but that's another issue. 🙂

[Megha166]

I would create a new measure with CALCULATE function and filter condition where VALUE <> SELECTEDVALUE(CATEGORY). If you can give exact names of fields, I can write the formulae for you.

[Anonymous]

Hi @Megha166 

This is my simplified schema that is quite common.

The general task is to show a table with top10 units by sales in the same category along with a filtered one.

If you can give me an idea of how to show at least units within the category, I will try to figure out the next action by myself.

Thank you!