Hello guys
With this measure i am trying to create a ranking by country.
The fact table has the value, the accounts, the entity (one country can have several entities), the date and the scenario (ACT, BUD etc).
In the results below "BUD" and "BY" are working.
"PY" is not working, because i did not managed to have the table pulling data from this year and last year (there is a filter set on the page to the current year).
If i take ALL, then there is way to much data (accounts etc).
I tried with ALLSELECTED as well, but no success 😞
Any hint on how i could solve this???
Ranking based on RANK 2h =
VAR _SelectedScenario =
SELECTEDVALUE ( 'Slicer Scenario'[ScenarioID] ) // NOT THE PARAMETER SCENARIO!!!
VAR _curCountry =
SELECTEDVALUE ( DimEntities[Country] ) // As SWITCH is expecting a scalar value, we need to have comprehensive statements below
// #1 Create the tables to perform the ranking upon
VAR _TableBUD =
SUMMARIZE (
ALLSELECTED ( 'Fact' ),
DimEntities[Country],
"Value1", [ACT Vs BUD]
)
VAR _TableBY =
SUMMARIZE (
ALLSELECTED ( 'Fact' ),
DimEntities[Country],
"Value2",
[Amount ACT]
- CALCULATE ( [Value_all], 'Fact'[Scenario] = "Budget" )
)
VAR _TableACT =
SUMMARIZE (
ALLSELECTED ( 'Fact' ),
DimEntities[Country],
"Value3", [Amount ACT]
)
VAR _TablePY =
SUMMARIZE (
ALL ( 'Fact' ),
DimEntities[Country],
"Value3", CALCULATE ( SUM ( 'Fact'[Value] ), 'Fact'[Scenario] = "Actual" )
) // #2 Create a column with the ranking on this virtual table
VAR _rankedTableBUD =
ADDCOLUMNS (
FILTER ( _TableBUD, [Country] <> BLANK () ),
"rankBUD", RANKX ( _TableBUD, [Value1],, DESC, SKIP )
)
VAR _rankedTableBY =
ADDCOLUMNS (
FILTER ( _TableBY, [Country] <> BLANK () ),
"rankBY", RANKX ( _TableBY, [Value2],, DESC, SKIP )
)
VAR _rankedTablePY =
ADDCOLUMNS (
FILTER ( _TablePY, [Country] <> BLANK () ),
"rankPY", RANKX ( _TablePY, [Value3],, DESC, SKIP )
)
RETURN
SWITCH (
_SelectedScenario,
"BUD", MAXX ( FILTER ( _rankedTableBUD, [Country] = _curCountry ), [rankBUD] ),
"BY", MAXX ( FILTER ( _rankedTableBY, [Country] = _curCountry ), [rankBY] ),
"PY", MAXX ( FILTER ( _rankedTablePY, [Country] = _curCountry ), [rankPY] )
)
Thanks in advance
G.
