Help with DAX formula required please

[pjandliz]

I need some DAX help please.

I have created a matrix visual of revenue by client (rows) and services (columns) - no problem there.

I have created another matrix which calculates the total value of my Top N clients, and the proportion of the Grand Total these Top N represent, again with the services as columns (so Total Revenue and % of Total as rows).

My problem is my measure to calculate the Top N revenue calculates the Top N based on the Top N values for each of the respective services whereas I want it to be based on the Top N clients' total revenue.

In the example below, my measure calculates the Top 3 for service A by totalling the 3 highest values found for clients with A services. However, I want to total service 'A' values of the 3 highest clients based on the Total column. 

Here's my current measure
(where [Revenue] is just a SUM() of the revenue field, and [Top N] is a measure returning the # of Top N clienst to sum for, eg 10):

Revenue Top N =

  CALCULATE (

  [Revenue],

  TOPN (

    [Top N],

    GROUPBY ( Data, Data[Client Name]),

    CALCULATE ( [Revenue], ALL(Data[Service]) )

  )

)

[pjandliz · Accepted solution]

Finally I have solved this. With some help from SQLBI's tutorial on Top N, I modified my TOP N formula, and had to clear the column filter for Service which the matrix was applying.

Here's my DAX for anyone interested.

Revenue Top N =
CALCULATE (
  [Revenue],
  TOPN (
    [Top N],
    ADDCOLUMNS (
      ALLSELECTED(Data[Client Name]),
      "Total Revenue",
      //calc revenue for all Services by clearing the Service column filter
      CALCULATE(
        [Revenue],
        ALLSELECTED(Data[Service])
        )
      ),
      [Total Revenue], DESC,
      Data[Client Name], ASC
    )
  )

[pjandliz]

Thanks for your suggestion @amitchandak.

However, I need to get the revenue number correct as it's displayed (and I can then calculate the % of total from it and another measure I already have working).

I did notice you've used ALLSELECTED()... I've not used that before so have used it instead of my ALL (after researching the function) however it didn't help.

[amitchandak]

@pjandliz , Based on what I got. Try % measure like

divide(calculate([Revenue],TOPN([Top N],allselected(Data[Client Name]),[Revenue],DESC), values(Data[Client Name]))
, calculate([Revenue],TOPN([Top N],allselected(Data[Client Name]),[Revenue],DESC)))