How to create a dynamic table/Column based on a ranking?

[SriGaG1]

Hello Folks,

I am trying to create a separate table based on ranking. As an example, when I select top 15 manufcturers from the provided slicers, , the table need to update top 15 manufacurers.  If I select All, the same will be applied and the table need to update all manufacturers. What I need is, from that table, it is required to pass relationships to other fact tables. 

Or If you can help me to create a separate calculated column based on the above requrement is okay for me. What business need is when the top N manufacturers selected from slicers, the same top N manufacturers need to show in the other pages. like 2019,2020 and 2021. 

You can download the pbix file from this link.

https://drive.google.com/file/d/19mbM68onjax2qxF0khwQfe11OnplO6YW/view?usp=share_link

[PaulDBrown]

Ok, I've checked out the file.

The first question is: do you need the table for each year as a seperate table for any particular reason?

If the tables have the same structure, I would model the data differently by creating a single table containing all the years. This would solve the problem you are facing very easily.

[SriGaG1]

@PaulDBrown thank you for the reply. Actually no. I have presented you something similer to my business requrement. So we cannot change the data model eventhe tables. these are really large tables actually. and thats y

[PaulDBrown]

In that case you are going to have to replicate all the measures for each of the years. You cannot create dynamic calculated tables or columns: these are calculated when the model is loaded, so they are impervious to filters and slicers. Anything dynamic has to be done with measures.

As regards the model, having a single fact table in your example is considered a best practice. It is not unusual to have fact tables with millions of rows.

[SriGaG1]

@PaulDBrown  thank you very much for the effort. Feel like you have re created the data model. But in the customer's requirement, e cannot do any of the single change to thedata model and it is really complex. They need to be in separate sheets. 

[PaulDBrown]

Sure, that's fine. 
Just replicate the measures for each year. If you add the slicers to each page, you can synch them (and then even hide the remaing ones if you so wish)

[SriGaG1]

@PaulDBrown  Thank you. I did it. But the issue is, the table in my original document has more than 50 columns . and sometimes the sorting dimension columns are getting duplicated. so when I apply the ranking column there, it is loading all the day and in the end, everything will be collapsed. I feel the column count is too much.  Can we do something like this?

Refer the final column, and based on that we do a ranking and we can apply it for the above. 

I have tried that one too. But allexcept function is not getting the exact value we need. 

In tableau, we do use, {FIXED [PRODUCTCODE]: [SORT_SLICER_VALUE]}. But in power BI, I have wrote the same like below

output = CALCULATE([SORT_SLICER_VALUE],ALLEXCEPT('ProductMaster',' ProductMaster'[PRODUCTCODE]))

SORT_SLICER_VALUE = SWITCH(SELECTEDVALUE('Slicers Des'[Slicer Des]),
"Price variance/ kg Original - Actual", [Price variance/kg Original - Actual],
"Price variance/ ctn Original - Actual", [Price variance/ctn Original - Actual])

But this calculation returns the same disaggregated value in the [SORT_SLICER_VALUE] measure.

how can we do that thing? you have any idea? i ran out of options.. 

[PaulDBrown · Accepted solution]

Try the following:

Output Required =
CALCULATE (
    SUM ( fTable[Sort Slicer Value] ),
    FILTER ( fTable, fTable[Product Code] = EARLIER ( fTable[Product Code] ) )
)

Even though the you have to keep the yearly tables separate in the model, are you allowed to create a new table in the model? You could create a new table with only the summary values for all the years