Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

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=i5ntr3jd38ssxt4xl04varic5

 

PBI File,

 

https://www.dropbox.com/s/a8f66j5w80xyr5h/FREQUENCY%20ND%20COUNT.pbix?dl=0

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

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

     

     

  • Fowmy's avatar
    Fowmy
    5 years ago

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

     

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

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

     

     

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

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

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Your measure solutions working well. 

       

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

       

      can you please advise.

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

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