Forum Discussion

PPStar's avatar
PPStar
Helper V
3 years ago

DAX Help. Rank or Filter

Hi, i have the following Measures

 

Usage - Consumer = var consumer = CALCULATE('Measures'[Usage - All], Activities[Role] = "Consumer")
Return
IF(ISBLANK(consumer),"0",consumer)
 
The UsageAll is just a count of the Audit Records. 
The Measure above is is just filter the count to show Consumer counts. This part all works fine. 
 
I then created another measure as below
Usage Consumer - Last Year = CALCULATE([Usage - Consumer], SAMEPERIODLASTYEAR(Dates[Date]))
 
This gives me the consumer usage for each workspace for the previous year. 
 
When i out the data into a matrix, i get the below
 

 

(I have blurred out the workspace name values). 

 

I need to create a graph which shows the top 5 most used workspaces from the previous year.  On the Matrix above, i have all the data, but i need to sort it to only show the the top 5 workspaces for each month. So looking at the matrix above, you can see the top 5 would be 

 

Workspace 1 = 3419

Workspace 2 = 372

Workspace 3 = 182

Workspace 4 = 182

Workspace 5 = 106

 

I want to plot these 5 workspaces on a stacked chart to show their usage every month. 

 

How can this be acheived?

 

Thanks

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    PPStar , try like

     

    CALCULATE([Monthly measure],TOPN(5,all(Table[Workspace]),[Last year measure],DESC),VALUES(Table[Workspace])))

    • PPStar's avatar
      PPStar
      Helper V
      3 years ago

      I dont understand what you have done there.  What is [MonthyMeasure]

       

  • PPStar's avatar
    PPStar
    Helper V
    3 years ago

    i need to find the top 5 workspaces per month, so the stacked graph needs to show me a stacked chart of 5 of the most users workspaces per month