Forum Discussion

gladies123's avatar
gladies123
Icon for Helper I rankHelper I
5 years ago
Solved

Reg:To display a serial number in table visual

Hi,

Kindly provide a solution to create serial no based on the v_ret_result and count of v_iden_no.

i have used following measure

indx_msr = RANKX(ALLSELECTED(cxsurrender[V_RET_RESULT]),CALCULATE(SUM(cxsurrender[Index])),,DESC,Dense) but it returns 1 in the total and it doesn't sort as per count.

 

thanks,

gladis

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, gladies123 

     

    Since your SUM function is not calculated based on the count of v_iden_no column, you are not getting the correct sort.
    Also, if your count column is aggregated data, then you may need to modify the formula to change the SUM function to a Count function, like the following.

     

    If you do not want to display the total value, one way is to turn off the total, and the other way is to modify the measure. As YukiK  mentioned above.

    measure would be like this:

    _indx =
IF (
    HASONEVALUE ( 'Table'[Count of V. _IDEN_ NO] ),
    RANKX (
        ALLSELECTED ( 'Table' ),
        CALCULATE ( COUNT ( 'Table'[Count of V. _IDEN_ NO] ) ),
        ,
        DESC,
        DENSE
    )
)

     

     The display result might look like this:

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • YukiK's avatar
    YukiK
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    You can hide totals in the visual settings or use something like:

    MeasureToDisplay=IF(HASONVALUE([ColumnOfTotal],[Measure],BLANK())

    Can you show your underlying table? So we can see what's going on.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, gladies123 

     

    Since your SUM function is not calculated based on the count of v_iden_no column, you are not getting the correct sort.
    Also, if your count column is aggregated data, then you may need to modify the formula to change the SUM function to a Count function, like the following.

     

    If you do not want to display the total value, one way is to turn off the total, and the other way is to modify the measure. As YukiK  mentioned above.

    measure would be like this:

    _indx =
IF (
    HASONEVALUE ( 'Table'[Count of V. _IDEN_ NO] ),
    RANKX (
        ALLSELECTED ( 'Table' ),
        CALCULATE ( COUNT ( 'Table'[Count of V. _IDEN_ NO] ) ),
        ,
        DESC,
        DENSE
    )
)

     

     The display result might look like this:

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.