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

     

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

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

     

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thanks for your reply and help.

       

      Your solution working well.