Need help to sum measures and create the top N

[Poma]

Hello all,
I have a table with "growth" from Multiple "Months" in different "Departments" ; A;B;C;D and E
The Growth has been calculated in a measure. Now I need to show the TOP N Departments based on the higest "Total" number. 
As seen in the image my Total is showing 0% instaed of sum of Totall Growth.  Can anybody help me to solve this problem? 

Thank you
 Cheers

[MFelix · Accepted solution]

Hi @Poma ,

Without any specific information is difficult to guide you in the correct path, has I refered measures are calculated in context so what is true for one visualization can be different for another.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

If you can share also what is the expected result, the growth values that you need, would be helpfull.

[MFelix]

Hi @Poma ,

Don't know how you have your percentage growth calculated but seems to me that this is a context base issue since I assume that the growth compares one month to the previous one or similar.

Measures are calculated in context so when you go to the total values the calculation is made on an aggregated level and not on the month/ department level.

In this case and since you refer that you want to calculate the sum of the growht you need to do a similar measure to this one:

Growth =
IF (
    HASONEVALUE ( Table[Month] ),
    [GrowthMeasure],
    SUMX ( VALUES ( Table[Month] ), [GrowthMeasure] )
)

Be aware that this may need some adjustment depending on your model and how you have your growth measure calculated.

[Poma]

Hi @MFelix 

Thank you so much for your response.

Right now I have the following top N chart with drop down to choose the number of months and departments.

But I need to show the chart in another way:
I need to show the Calculation of growth across the selected number of months (in this case 2). TOP 2 departments will be the one´s with the highest growth across all 2 months. So, I don’t want to compare month by month, compare between the departments across the entire timeframe that has been selected (in this case 2 months).

I’m really confuse now and have no idea how to solve it. I’ve tried to use your suggested dax but I don’t think Im getting the correct result by it or am I wrong?

Here is the result when I use your dax and rank on top of it:

I have many blank fields so the first one with a value inside starts from rank 75 and not 1 ☹

Your help is really appriciated 

[MFelix · Accepted solution]

Hi @Poma 

To make the calculation you need and based on the information you have shared you need to create the following measures:

SUMXGrowth = 
SUMX ( VALUES ( TableDate[Date].[Month] ), [M_Incident growth] )

filtertopn service =
IF (
    RANKX (
        ALL ( Table[service] ),
        CALCULATE ( [SUMXGrowth], ALLSELECTED ( TableDate ) )
    )
        <= MAX ( TopServiceSelectionTable[Service] ),
    1
)

Now use the first measure for the values on your chart and the second one to filter the visualization in this case you need to place the measure in the filter pane and select all non blanks.

[MFelix · Accepted solution]

Hi @Poma ,

Without any specific information is difficult to guide you in the correct path, has I refered measures are calculated in context so what is true for one visualization can be different for another.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

If you can share also what is the expected result, the growth values that you need, would be helpfull.