Fill Columns based on rank measure

[WMansueto]

Hello guys,

I've been searching from a week now and still didn't find anything that helped me with this one,

I want to have a vision of my batch numbers and the cost for each item,

Since Power BI's matrix visual has a column limit, I've created a table with 9 rows and ranked my batch numbers by date

If i slice the data by date I has to fill from left to right
The problem is it work only if has one rank from each item

PBIX file:
https://drive.google.com/file/d/1VJxuo9N6GpC8uexMlyk-e8cdDnMYG006/view?usp=sharing

[StrategicSavvy]

Hi, 

I think that it requires different approach to solve this kind of problem. I would go for calculated column in this specific case as you would like to return information not processing them. 

I created calculated column in Batches calles Rank and instead of using measures I would depend on existing + calulated column.  In aggregations it says First batch number but in fact it will be always the one you slice because matrix is operating on the lowest grain - there are no aggregations. 

I attach PBIX.

https://drive.google.com/file/d/1TKXXNAb2xpZoDwMyG5unD_S7eSM0ndLI/view?usp=sharing 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron 

[WMansueto]

This kind of work but I have to fill the left gaps,
And in my production dashboard i have 100+ batches and the matrix visual is limited to 100 columns,

So, i have to limit only 20 or 30 batches at time.

[v-rzhou-msft · Accepted solution]

Hi @WMansueto ,

Please try code as below to create [Cost] and [Batch] measures.

Cost = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED ( Batches ),
        Items[ItemName],
        Batches[BatchNumber],
        Batches[Cost],
        "RANK", [Rank]
    )
VAR _Costs =
    SUMX (
        FILTER (
            _SUMMARIZE,
            [ItemName] = MAX ( Items[ItemName] )
                && [RANK] = MAX ( 'Columns'[IdColumn] )
        ),
        [Cost]
    )
RETURN
    IF ( _Costs = BLANK (), "error", _Costs )

Batch = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED ( Batches ),
        Items[ItemName],
        Batches[BatchNumber],
        Batches[Cost],
        "RANK", [Rank]
    )
VAR _BatchNumbers =
    MAXX (
        FILTER (
            _SUMMARIZE,
            [ItemName] = MAX ( Items[ItemName] )
                && [RANK] = MAX ( 'Columns'[IdColumn] )
        ),
        [BatchNumber]
    )
RETURN
    IF ( _BatchNumbers = BLANK (), "error", _BatchNumbers )

Result is as below.

By Default:

With Date filter:

Best Regards,
Rico Zhou

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

[WMansueto]

This works perfectly, thank you so much

[lbendlin]

If you rank them by date then why not include the date in the matrix?