Forum Discussion

TaoW's avatar
TaoW
Regular Visitor
1 year ago
Solved

Question about the RANKX and accumulated number

Hi , guys 
I met some problems about the "rankx"function and  calculating accumated number.

 

Dimension table: Product (RPN, Divison)       Fact table :Sales( Productrpn，Sales)

Relationship:     RPN  1-* Productrpn
1.  RPN's quantity > Productrpn quantity .And unfortunately , i have some negative sales numbers. so when i tried to sort them in sales amount. Those RPN which has no sales appeared and occupied many positions. how should i ignore them?

 

2. I replace "RPN" by using "Productrpn"in fact table, still can't avoid of this situation,so i "summarize"a new table in the "rankx". it works. But under this visual i don't know how to write the formula to calculate the accumulated sales amount cause it's related to 2 fields from 2 tables.

*I must finish this in the visual. I konw using table functions(summarzie etc.)to create a auxiliary table and create a new calculated column maybe helpful, but this kind of new table can't be effect by the calender slicer.  Plz give me some advices. Thanks!

 
Rank RPN: IF(HASONEVALUE(Sales'[productRPNDesc]),RANKX(SUMMARIZE(ALL(Sales'),'Sales'[productRPNDesc],'Product'[Division]),[Sales CC],,DESC))

 


 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi TaoW ,

     

    As far as I understand, you need to ignore null values ​​during ranking and implement dynamic ranking based on date slicer. If my understanding is correct, you can see the following steps:

     

    1.This is the simple data I created:

     

    2.Create two measures:

    rank = 
IF(
    HASONEVALUE('Product'[RPN]) && MAX('Sales'[Sales]) <> BLANK(),
    RANKX(
        FILTER(
            ALL('Sales'),
            'Sales'[Sales] <> BLANK() && 'Sales'[Year] IN VALUES('Sales'[Year])
        ),
        CALCULATE(SUM('Sales'[Sales])),
        ,
        DESC
    )
)
    Cumulative Sales by Rank = 
VAR _currentrank = [rank]
RETURN
    IF (
        HASONEFILTER ( 'Product'[Division] ),
        CALCULATE (
            SUM ( 'Sales'[Sales] ),
            FILTER ( ALLEXCEPT ( 'Sales', 'Sales'[Year] ), [rank] <= _currentrank )
        ),
        SUMX ( 'Sales', 'Sales'[Sales] )
    )

     

    3.The results are as follows:

     

    Best Regards,
    Zhu
    Community Support Team

     

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

  • TaoW's avatar
    TaoW
    Regular Visitor
    1 year ago

    Thanks for your reply.  But in the real situation,those rpns which have no sales records didn't exsit in the fact table. in your example, "C“”H“"K""P"shouldn't be in the table2 . RPNs qty> productrpn qty. You can't sell all kinds of RPNs in several years. So when i wanted to rank them,
    1)if i use the "RPN"field, i had to face those blank lines as i showed in the question1.
    2)if i use the field "productrpn", i must use "SUMMARIZE" to create a new table for ranking (use "crossjoin"will also face the same problem:blank lines join in the ranking).In this situation, "productrpn"&"Division"are from different dimensions, the calculation of accumulated number will become complicated.   
    * I try to use the "runningsum"and "windows" functions to calculate it directly but all failed. Under the traditional way, i don't know how to write among multiple fields which are from different tables.