Forum Discussion
DAX - Most Frequent value
Hi,
To your Table visual, drag the Item field and write these mesures
User count = counta(Data[User])
Most frequency = FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count]),1)
Drag the second measure to your visual.
I am confused. In the Group column, you do not have any entry as A at all. Do not be in a hurry to post. Read your own question multiple times before posting. What exact result do you expect to see in the sub total row and why?
I am hoping I could show the users, for each QTY,
1.)which supplier with the lowest cost & the cost (has been achieved)
2.) Before Expanding the QTY, could it show the most frequenct supplier from all the QTY.
Like your provided measure, except the filter component is a Measure rather a column now.
**Filter "Rank by Supplier" = 1
Supplier 1 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Table[Group]),[User count],DESC),1),[Rank by Supplier]=1)
For this case, the supplier 1's result should be "A" since "A" is the most frequent supplier in all QTY
The reason of doing this is letting the user know generally, which supplier should be used since that supplier with the lowest cost for most cases.
May I know is it achievable?
I still do not understand your expected result. Someone else who does will help you.
Ashish_Mathur No worries. You did help a lot. Great thanks for that.