Showing Top Department for Same Period Last Year Help

DAX DAX Calculations
1

HI Team,

I need some help with how to visualize the top non blank department for same period last year.

Data model is clean - Fact table connected to date dim.
The page im working on is filtered to current year.

Measures:
Total Contracts = DISTINCTCOUNTNOBLANK(Contract[ContractID])
Total Contracts SPLY = CALCULATE([Total Contracts], SAMEPERIODLASTYEAR(‘Date’[Date]))

What I want to do is in a card visual show the top department for this year
and show the top department for the previous year.

Putting the data into a table I can get what I want. Obviously If I just want to show the Department name in a card visual I have to take out the measure and then the context is lost. It makes sense you have to have the measure there. So is there a way to display only the department name.

I got to this measure but still does not work ??? any suggestions, I dont have a plan :frowning:

Department SPLY = 
VAR FilteredTable = 
    FILTER(
        ADDCOLUMNS( 
            VALUES(Contract[Department]), 
            "RankX_TotalContractsSPLY", 
            RANKX(
                FILTER(
                    ALL(Contract[Department]),
                    Contract[Department] <> BLANK()
                ),
                [Total Contracts SPLY],
                ,
                DESC, Dense
            ) 
        ),
        [RankX_TotalContractsSPLY] = 1
    )
RETURN
    MAXX(FilteredTable, Contract[Department])
2

Hi @kyle.jardine40 ,

Iteration function in PowerBI calculate for each row in the table. I suppose for your post that you would like to have different context.
Maybe you can check TopN

You can check Data Mentor for more details.

If you still have problems with the context, could you provide PBIx sample for better / detailed help ?

3

Hi @kyle.jardine40 - Please share the PBIX file to check.

Thanks
Ankit J

4

Thanks For the suggestion guys!
So diving into this problem further I found that what I’m trying to do is not right.

I came across a scenario where there are ties i.e. 3 different dept with the same number of contracts SPLY. Because of this it just the wrong visual to use. I’m not going to proceed with this any further.

5

Please closed the problem by marking it solved.
thanks
Keith

1 Like