How to search out or identify the top index month from virtual table

[Anonymous]

Hi, Expert, I recently met a problem, the detailed background is I have an original phsical table which is customer sales order list base on actual happened days, and I need to caculate and demo the index by quarterly basis; I could use measue([0-index]) to caculate the index, but I still want to find out the maximum index happened quarter.

I use below DAX to identify and demo the maximum index. however, is there any solution to also find out the relative quarter of maximum index? I try DAX lookupvalue but failed because of this DAX is disable for virtual table, could you pleae help to suggest better solution? Thanks very much.

var maxtable=ADDCOLUMNS(DISTINCT('Customer sales lit'[Quarter Label]),"New Index",[0-index])

return

MAXX(maxtable,[New Index])

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks for reply😁

I simulated part of my original sales order list as below picture and the fourth column is added by using DAX, the index metric is (total sale quantity in Q4-total sale quantity in Q3)/(YTD total sales quantity). And as upper message, I now could identify the maximum index by using virtual table, and I'm still wondering is there any solution to also show or identify directly the maximum index relatvie quarter? Then I could use "Card" to show the result of specific time period or list results in "table" or "matrix" with "Calendar Year" as one of the column

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below to get the index

0-index = 
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Order Quantity] ) ),
    ,
    ASC,
    DENSE
)

2. Create a measure as below to get the max of index 

Measure = 
VAR _tab =
    ADDCOLUMNS ( 'Table', "@index", [0-index] )
RETURN
    MAXX ( _tab, [@index] )

Best Regards

[Anonymous]

Hi Dear, thanks for reply.  I tested and worked, however, I also want to get directly the quarter information which is the top quarterly basis indext. Is there any solution?