Reply
Saxon10
Post Prodigy
Post Prodigy
Partially syndicated - Outbound

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)),""))

 

 

Saxon10_1-1622488360166.png

 

 

 

 

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

 

2 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

Syndicated - Outbound

@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]
)

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Syndicated - Outbound

@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) )
)

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
Fowmy
Super User
Super User

Syndicated - Outbound

@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]
)

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Syndicated - Outbound

Your measure solutions working well. 

 

is that possibile can I get the new calulate column option inculding date range?

 

can you please advise.

Syndicated - Outbound

@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) )
)

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Syndicated - Outbound

Thanks for your reply and help.

 

Your solution working well.

 

Syndicated - Outbound

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). 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

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.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)