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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft 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 )