Forum Discussion
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 separated. For example
Orange
Orange
Orange
Orange
Banana
Banana
Banana
Kiwi
Kiwi
Plum
Plum
So, every version of count and/or sum I have tried gives me a total of 12 here for all items, instead of returning results like the following:
Orange 4
Banana 3
Kiwi 2
Plum 2
I know this can be accomplished by visualizations, but I need either a measure or column that I can create that will would with the following formula:
RANKX(ALLSELECTED(Query [Fruit]), [Total Fruit Count], , DESC)
This formula is returning a 1 value for all of my items, even though in visualizations it is returning the proper ranking. Any help would be appreciated!
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
9 Replies
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
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?
BhaveshPatel actually I figured out how to flip it! RankingColumn= COUNTROWS(FILTER(MyTable,EARLIER([TotalRows])<[TotalRows]))+1
If possible, I'd like to understand how to deal with ties in this equation but this was the correct solution. Thanks!
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 separated. For example
Orange
Orange
Orange
Orange
Banana
Banana
Banana
Kiwi
Kiwi
Plum
Plum
So, every version of count and/or sum I have tried gives me a total of 12 here for all items, instead of returning results like the following:
Orange 4
Banana 3
Kiwi 2
Plum 2
I know this can be accomplished by visualizations, but I need either a measure or column that I can create that will would with the following formula:
RANKX(ALLSELECTED(Query [Fruit]), [Total Fruit Count], , DESC)
This formula is returning a 1 value for all of my items, even though in visualizations it is returning the proper ranking. Any help would be appreciated!
Minor Edit: The simplest way to ask this, now that I think about it, is I need something that performs what Excel does when you create a Pivot table, if possible.
Hey,
first - welcome to this community.
I'm not sure if I understand your requirement correctly, but why not just create a measure that counts the items like in this screenshot:
Hope this is what you are looking for
Regards
Tom
A simple Count is giving problems with my second measure, which is counting all items, and not separating them out. This is hampering visuals I am working on, which require the ranking to be accurate. The ranking is returning all ones, as seen below, because it is working off the total count.
Hi HungryPowerBIer,
You are writing rank as a column. Wite it as a measure
=RANKX(ALL(Query[Fruit]),[Just a count])
Does this work?
