- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Frequency Count
I have a two tables are data and report. In data table contain Date, Item and Vendor.
Report table contain item only. (Item stored as text format. Item column data type is mixed – Sometimes number only or number and text together)
In-between two tables the item column are common.
I would like to get the most frequency vendor according to the item based on the data ranges (From April 2020 to April 2021)
Data:
DATE | ITEM | Vendor |
22/04/2020 | 125 | A1 |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AE |
22/04/2020 | 125 | AR |
24/04/2020 | 4568 | H1 |
24/04/2020 | 4568 | H1 |
24/04/2020 | 4568 | H1 |
24/04/2020 | 4568 | H1 |
24/04/2020 | 4568 | H1 |
24/04/2020 | 4568 | H1 |
24/04/2020 | 4568 | H3 |
24/04/2020 | 4568 | H3 |
24/04/2020 | 4568 | H3 |
24/04/2020 | 4568 | H3 |
Report:
ITEM | Vendor FREQUENCY |
125 | AE |
4568 | H1 |
In Excel I am applying the following array formula =IF($F4="","",IFERROR(INDEX(C:C,AGGREGATE(14,6,ROW($A$3:$A$67)/(MAX(FREQUENCY(IF($A$3:$A$67>=$E$2,IF($A$3:$A$67<=$E$3,IF($B$3:$B$67=$F4,IF($C$3:$C$67<>"",MATCH($B$3:$B$67&$C$3:$C$67,$B$3:$B$67&$C$3:$C$67,0))))),ROW($B$3:$B$67)-2))=FREQUENCY(IF($A$3:$A$67>=$E$2,IF($A$3:$A$67<=$E$3,IF($B$3:$B$67=$F4,IF($C$3:$C$67<>"",MATCH($B$3:$B$67&$C$3:$C$67,$B$3:$B$67&$C$3:$C$67,0))))),ROW($B$3:$B$67)-2)),1)),""))
I would like to get the same result in Power BI. I am looking for New calculated column option.
Any advise please.
Excel File:
Here is the excel file for your refence https://www.dropbox.com/scl/fi/c4e55usqn9ej36bum5acp/FREQUENCY-COUNT-31-05.21.xlsx?dl=0&rlkey=i5ntr3...
PBI File,
https://www.dropbox.com/s/a8f66j5w80xyr5h/FREQUENCY%20ND%20COUNT.pbix?dl=0
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Saxon10
I created a measure, you can add the ITEM column from Report and the measure. Check the attached file. You can use the slicer to filter for any date period.
Freq =
MAXX(
TOPN(
1 ,
ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
),
[Vendor]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Saxon10
Please find below the formula for the calculated Column that will return only for available vendors for April 2020 to April 2021.
Freq Column =
CALCULATE(
MAXX(
TOPN(
1 ,
ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
),
[Vendor]
),
FILTER(all(DATA[DATE]), DATA[DATE] >= DATE(2020,4,1) && DATA[DATE] <= DATE(2021,4,30) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Saxon10
I created a measure, you can add the ITEM column from Report and the measure. Check the attached file. You can use the slicer to filter for any date period.
Freq =
MAXX(
TOPN(
1 ,
ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
),
[Vendor]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your measure solutions working well.
is that possibile can I get the new calulate column option inculding date range?
can you please advise.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Saxon10
Please find below the formula for the calculated Column that will return only for available vendors for April 2020 to April 2021.
Freq Column =
CALCULATE(
MAXX(
TOPN(
1 ,
ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
),
[Vendor]
),
FILTER(all(DATA[DATE]), DATA[DATE] >= DATE(2020,4,1) && DATA[DATE] <= DATE(2021,4,30) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your reply and help.
Your solution working well.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your reply.
Is there any chance can I get the New calculate column instead of measure because based on the result column (Frequency).
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
06-28-2024 06:53 PM | |||
07-30-2024 03:27 PM | |||
07-23-2024 08:08 AM | |||
07-28-2019 08:04 PM | |||
12-11-2024 07:09 PM |
User | Count |
---|---|
105 | |
75 | |
44 | |
39 | |
33 |
User | Count |
---|---|
165 | |
90 | |
65 | |
46 | |
43 |