Forum Discussion

Glaeran's avatar
Glaeran
Frequent Visitor
9 years ago
Solved

Show bottom/top value on KPI / Card

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..

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

    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

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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.:smileyhappy:

    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's avatar
      Lah123
      Frequent Visitor
      7 years ago

      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 )