Forum Discussion
fabiocovre
Advocate I
8 years agoTop N and Others - monthly
Hi, I want to create the graph below, where it will show the top 5 specialties by cost in the period (I did it manually, by grouping, but these 5 specialties should change, depending the date fil...
- 8 years ago
Hi@ fabiocovre
You can use this measure as below:
Specialtie Rank 2 = VAR allMonthYear = CALCULATE ( COUNT ( 'Table'[MonthYear] ), ALL ( 'Table' ), ALL ( Example ) ) VAR selectedMonthYear = CALCULATE ( COUNT ( 'Table'[MonthYear] ), ALL ( Example[MonthYear] ) ) RETURN IF ( HASONEVALUE ( 'Table'[MonthYear] ), RANKX ( ALL ( Specialties[Specialties] ), CALCULATE ( SUM ( Example[Claim] ) ) ), IF ( selectedMonthYear > 1 && selectedMonthYear < allMonthYear, RANKX ( ALLSELECTED ( Specialties[Specialties] ), CALCULATE ( SUM ( Example[Claim] ), ALL ( 'Example'[MonthYear] ) ) ), IF ( selectedMonthYear = allMonthYear, RANKX ( ALL ( Specialties[Specialties] ), CALCULATE ( SUM ( Example[Claim] ), ALLEXCEPT ( Example, Specialties[Specialties] ) ) ), RANKX ( ALL ( Specialties[Specialties] ), CALCULATE ( SUM ( Example[Claim] ), ALLEXCEPT ( Example, Specialties[Specialties] ) ) ) ) ) )And when creating a relationship between Example and Table , setting cross filter direction: Single
Result:
Here is demo, please try it
https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0
Best Regards,
Lin
v-lili6-msft
Community Support
8 years agoHi@ fabiocovre
You can use this measure as below:
Specialtie Rank 2 =
VAR allMonthYear =
CALCULATE ( COUNT ( 'Table'[MonthYear] ), ALL ( 'Table' ), ALL ( Example ) )
VAR selectedMonthYear =
CALCULATE ( COUNT ( 'Table'[MonthYear] ), ALL ( Example[MonthYear] ) )
RETURN
IF (
HASONEVALUE ( 'Table'[MonthYear] ),
RANKX ( ALL ( Specialties[Specialties] ), CALCULATE ( SUM ( Example[Claim] ) ) ),
IF (
selectedMonthYear > 1
&& selectedMonthYear < allMonthYear,
RANKX (
ALLSELECTED ( Specialties[Specialties] ),
CALCULATE ( SUM ( Example[Claim] ), ALL ( 'Example'[MonthYear] ) )
),
IF (
selectedMonthYear = allMonthYear,
RANKX (
ALL ( Specialties[Specialties] ),
CALCULATE (
SUM ( Example[Claim] ),
ALLEXCEPT ( Example, Specialties[Specialties] )
)
),
RANKX (
ALL ( Specialties[Specialties] ),
CALCULATE (
SUM ( Example[Claim] ),
ALLEXCEPT ( Example, Specialties[Specialties] )
)
)
)
)
)And when creating a relationship between Example and Table , setting cross filter direction: Single
Result:
Here is demo, please try it
https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0
Best Regards,
Lin
- fabiocovre8 years ago
Advocate I
Lin,
You are amazing. Thank you very much for that!!!
Best regards,
Fabio