Forum Discussion
Maximum of Count
Hello, I am new to power bi. I am facing a problem. I want to create a new column as "Highest Target Quantity" which shows for Jan 1st Highest Target Quantity achieved as 2 and for Jan 2 Highest Target Quantity achieved as 3, based on the Max Count column.
Hi Anonymous,
One sample for your reference. Here we can create two measures as below to get the result as we need.
MAX = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[DATE]),VALUES(Table1[ITEM]))
MAXX = MAXX(Table1,[MAX])
For more details, please check the pbix as attached.
Regards,
Frank
Hi Anonymous,
To create another new measure and make the table visual filterd by the measure.
Measure = var result = MAXX(ALLEXCEPT(Table1,Table1[DATE]),[MAXX]) return IF([MAX]=result,1,0)
Regards,
Frank
9 Replies
- Greg_Deckler
Community Champion
If you include Month and Day in your visual then you should just need a measure that is essentially:
Measure = MAX([Max COUNT])
Hello Greg, thank you for your time to reply to my issue. But I think I dint explain the problem clearly. The image attached is the file which needed to be solved. In the final output colum I want to get the Maximum Count for each date. Example : For Jan1 its team 3 with 2 count and for Jan 2 its team 2 with 3 count. If I use = Max([Count]) , it gives the output in visualization as 1 for all rows.
So I want the final output as Jan 1 Highest count as 2 and for Jan 2 as 3.
v-frfei-msftCommunity Support
Hi Anonymous,
One sample for your reference. Here we can create two measures as below to get the result as we need.
MAX = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[DATE]),VALUES(Table1[ITEM]))
MAXX = MAXX(Table1,[MAX])
For more details, please check the pbix as attached.
Regards,
Frank
- Ashish_Mathur
Super User
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[Max Count]),FILTER(Data,Data[Month]=EARLIER(Data[Month])&&Data[Day]=EARLIER(Data[Day])))
Hope this helps.
