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

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