Show bottom/top value on KPI / Card

[Glaeran]

Hey!

Here's a quick sneak-peak on my current model:

What I would like to show is a single value Card or KPI for:

1) Top Performing Prodcut Category

2) Worst Performing Product Category

Steps taken:
1) Calculate value POS_AMT_LW:

POS_AMT_LW = LOOKUPVALUE(POS[POS_AMT]; POS[END_DATE]; POS[END_DATE_LW]; POS[STORE_ID]; POS[STORE_ID]; POS[PRODUCT_ID]; POS[PRODUCT_ID])

2) Calculate POS_AMT_EXPECTED (we're assuming it's POS_AMT_LW * 1.05:

POS_AMT_EXPECTED = IF(POS[POS_AMT_LW] = 0; POS[POS_AMT]; POS[POS_AMT_LW] * 1,05)

3) Calculate POS_REALIZAITON:

POS_REALIZATION = IF(POS[POS_AMT_EXPECTED] = 0; 1; POS[POS_AMT] / POS[POS_AMT_EXPECTED])

Sneak-peak on final data:

Next steps:

However value shown by visual is completely incorrect as it should be for different category..

Any idea how to achieve what I would like to achieve or what I'm doing wrong?

My second try was to achieve exactly the same thing but using Calculated table:

Aggregate = SUMMARIZE(POS; 'Product Hierarchy Lookup'[PRODUCT_CATEGORY_NAME]; POS[END_DATE]; "POS_AMT"; sum(POS[POS_AMT]); "POS_AMT_LW"; sum(POS[POS_AMT_LW]))

With later on calculating POS_REALIZAITON as:

POS_REALIZATION = IF('Aggregate'[POS_AMT_LW] = 0; 1; 'Aggregate'[POS_AMT] / ('Aggregate'[POS_AMT_LW]*1,05))

However I'm unable to filter data inside Aggregate table by Slicers or other visuals that are on the report..

[v-ljerr-msft · Accepted solution]

Hi @Glaeran,

Have you tried using TOPN function(DAX) to create a new measure to calculate the first/last Product Category Name directly, then just show the measure on a Card in your first try? 

The formula below is for your reference.

First Product Category Name =
VAR table1 =
    TOPN (
        1;
        'Product Hierarchy Lookup';
        SUMX ( RELATEDTABLE ( POS ); 'POS'[POS_REALIZATION] )
    )
RETURN
    FIRSTNONBLANK ( table1[PRODUCT_CATEGORY_NAME]; 1 )

Last Product Category Name =
VAR table1 =
    TOPN (
        1;
        'Product Hierarchy Lookup';
        SUMX ( RELATEDTABLE ( POS ); 'POS'[POS_REALIZATION] ); 1
    )
RETURN
    FIRSTNONBLANK ( table1[PRODUCT_CATEGORY_NAME]; 1 )

Regards

[Lah123]

Hi! 

This does not seem to work for me. I get an error that Table1 does not exist. I'm trying to get the user that has the most candidates. Can you help me with this?

I tried it like this:

Top Name Can = 
VAR table1 =
    TOPN (
        1;
        'CustomerUsers';
        SUMX ( RELATEDTABLE ( Candidates ); 'Candidates'[Count] )
    )
RETURN
    FIRSTNONBLANK ( table1[Name User]; 1 )