Forum Discussion

HungryPowerBIer's avatar
HungryPowerBIer
Frequent Visitor
8 years ago
Solved

Count Multiple items by the item

Hello all! I am a bit new to Power BI and I am looking for a forumla to help me with another formula. I have items that I need to count, that have the same name and are linked to data that cannot be ...
  • BhaveshPatel's avatar
    BhaveshPatel
    8 years ago

    Hi HungryPowerBIer,

     

    You can also use the alternative approach by creating a new table on top of your base table.

    Go to the Data tab on the left hand side, then go to modelling tab and under the calculations, you would see the new table.

    Select and Paste the below DAX Syntax,

     

    MyTable= SUMMARIZE(BaseTable,
                                            BaseTable[Fruit],
                                              "TotalRows",COUNTROWS(BaseTable))

     

    And then create a calculated column in your newly created MyTable using the below syntax to get the ranking of the products.

    RankingColumn= COUNTROWS(FILTER(MyTable,EARLIER([TotalRows])>[TotalRows]))+1

     

    Thanks,

    Bhavesh

     

     

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
8 years ago

Hi HungryPowerBIer,

 

You can also use the alternative approach by creating a new table on top of your base table.

Go to the Data tab on the left hand side, then go to modelling tab and under the calculations, you would see the new table.

Select and Paste the below DAX Syntax,

 

MyTable= SUMMARIZE(BaseTable,
                                        BaseTable[Fruit],
                                          "TotalRows",COUNTROWS(BaseTable))

 

And then create a calculated column in your newly created MyTable using the below syntax to get the ranking of the products.

RankingColumn= COUNTROWS(FILTER(MyTable,EARLIER([TotalRows])>[TotalRows]))+1

 

Thanks,

Bhavesh

 

 

HungryPowerBIer's avatar
HungryPowerBIer
Frequent Visitor
8 years ago

BhaveshPatelThis worked but for two things: 

1. The ranking came back in reverse order. My highest count, came in with the highest number, instead of the highest count, showing as number 1. Is this because of the "Earlier" portion of COUNTROWS(FILTER(MyTable,EARLIER([TotalRows])>[TotalRows]))+1 ?

2. It doesn't seem to handle ties well, and duplicates the numbers, instead of continuing down or skipping like Rankx does. It's not likely to happen with my dataset but just in case it does, is there anyway to change that behavior?