Multiple Grouping & Sorting With Slicer

[dannyh246]

I have a fact table called FactAccumulation, which contains appox 500million records.

I have a dimension table called dimZone with about 50 records. 

Both are defined as follows. 

[FactAccumulation]

[Zone Key] [int] 
[Event Id] [int]
[Year] [int]
[Loss Usd] [decimal]

[DimZone]

[Zone Key] [int] 

[Zone Name] [VARCHAR] 

I want to be able to pick from a drop down (slicer) any number of Zones. Once i have the Zones selected, i want to 

1. Group the fact table on Zone & Year. (and summing up the Loss usd)

2. With the dataset from step 1, for each Zone Year pick the MAX loss value.

3. From the datatset in step 2 (Zone, Year, Max loss values), i want to partition by Zone order by Loss value descending and have row number appended.

In SQL it's fairly easy...something like this..

SELECT ROW_NUMBER() OVER(PARTITION BY occ.ZoneId ORDER BY occ.NetLossUsd DESC) AS RowNumber
             ,occ.[Year]
             ,occ.[ZoneId]
             ,occ.[NetLossUsd]
FROM
(
           SELECT e.[ZoneId]
                       ,e.[Year]
                       ,MAX(e.[NetLossUsd]) AS NetLossOurShareUsd
           FROM
            (
                   SELECT a.[EventId]
                               ,a.[ZoneId]
                               ,a.[Year]
                              ,SUM(a.[NetLossUsd]) AS NetLossUsd
                   FROM FactAccumulation a
                   GROUP BY a.[EventId]
                                     ,a.[AccumulationPerilId]
                                     ,a.[Year]
            ) e
            GROUP BY e.[ZoneId]
                             ,e.[Year]
            ) occ

How would i do this in PowrBi / DAX?

Any help would be much appreciated!!

Thanks

[v-jiascu-msft]

Hi @dannyh246,

Please check out the demo here. 

1. Create a calculated table as MiddleTable.

MiddleTable =
SUMMARIZE (
    'FactAccumulation',
    DimZone[Zone Key],
    FactAccumulation[Event ID],
    FactAccumulation[Year],
    "Total", SUM ( FactAccumulation[Loss USD] )
)

2. Create a measure for row numbers.

RowNumEqually =
RANKX (
    ALL ( 'MiddleTable'[Year] ),
    CALCULATE ( MAX ( 'MiddleTable'[Total] ) )
)

3. Create a table visual. The choose calculation type Maximum for column "Total".

Best Regards,

Dale