Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

ranking over virtual table

Hi, I want to create a ranking based on my virtual table. The aim of this analysis is to use the data from a virtual table to create a ABC analysis. for now I'm tryng to abstract and at least create a ranking

 

EVALUATE
VAR id_franchise_group  = 99
VAR GRP = SUMMARIZE(
    FILTER(
    dim_store,[id_franchise_group] = id_franchise_group
    	   ),
dim_store'[FullName],
"value", CALCULATE(sum(fact_table'[price]))),
"amount", CALCULATE(sum(fact_table[price]),ALLSELECTED(dim_store[FullName]))
))

//RETURN GRP	
VAR Total = ADDCOLUMNS(GRP,"RANKING",RANKX(all(GRP[FullName]),CALCULATE(sum(GRP[value]),,DESC,SKIP))) 
RETURN Total

 

basically I'm trying to create a table with a value per store and ranking it

FullNameValueAmountRanking
store A30501
store B15502
Store C5503

 

I just trying to use a virtual table after summarize to abstratct the Idea, but looks like I'm not able to use a virtual table with ranking function.

Some Idea?

Thanks

Need Help
Show and Tell
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

    I just modify your codes by correcting some pairs of single quotes and parentheses, and they seem work well.

    EVALUATE
VAR id_franchise_group = 99
VAR GRP =
    SUMMARIZE (
        FILTER ( dim_store, [id_franchise_group] = id_franchise_group ),
        dim_store[FullName],
        "value", CALCULATE ( SUM ( fact_table[price] ) ),
        "amount", CALCULATE ( SUM ( fact_table[price] ), ALLSELECTED ( dim_store[FullName] ) ),
        "RANKING",
            RANKX (
                ALL ( dim_store[fullname] ),
                CALCULATE ( SUM ( fact_table[price] ) ),
                ,
                DESC,
                SKIP
            )
    )
RETURN GRP

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    when I do in a actual table something goes wrong, despite that I need to work with this table after to make some calculations:

    EVALUATE
VAR id_franchise_group  = 99
VAR GRP = SUMMARIZE(
    FILTER(
    dim_store,[id_franchise_group] = id_franchise_group
    	   ),
dim_store'[FullName],
"value", CALCULATE(sum(fact_table'[price]))),
"amount", CALCULATE(sum(fact_table[price]),ALLSELECTED(dim_store[FullName])
"RANKING",RANKX(all(dim_store[fullname]),CALCULATE(sum(fact_table'[price]),,DESC,SKIP))
)

RETURN GRP

     and now my ranking is duplicating

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

      Hi Anonymous 

      I just modify your codes by correcting some pairs of single quotes and parentheses, and they seem work well.

      EVALUATE
VAR id_franchise_group = 99
VAR GRP =
    SUMMARIZE (
        FILTER ( dim_store, [id_franchise_group] = id_franchise_group ),
        dim_store[FullName],
        "value", CALCULATE ( SUM ( fact_table[price] ) ),
        "amount", CALCULATE ( SUM ( fact_table[price] ), ALLSELECTED ( dim_store[FullName] ) ),
        "RANKING",
            RANKX (
                ALL ( dim_store[fullname] ),
                CALCULATE ( SUM ( fact_table[price] ) ),
                ,
                DESC,
                SKIP
            )
    )
RETURN GRP

      Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as the solution to help other members find it.