locked
Advertiser Frequency - DAX Help? RRS feed

  • Question

  • Hi,

    I am wanting to make a Advertiser Frequency report for those that advertise with us.

     

    I have my calendar table

     

    Date               Month       Year          Week of Year

    20/01/2017   Jan             2017          3

     

    I have my transaction table

     

    Date              Company          Title                    Revenue

    20/01/2017   Bruce Forsyth      Comedy Gold      £50

     

    I am wanting to have a table/matrix that tells me how many customers spend once a week.  This will need to check the weeks somehow... any ideas?

     

     

    so it looks like

     

     

    Is there any way I can get this done via DAX?


    How I have done it manually is to create a pivot table with the company name in the rows and weeks in the column .  Then I do a count across the rows.  Then a count if in that row total column for 

     

    How many 1's twos etc.

     

    Must be an easier way?

    Thursday, October 12, 2017 7:36 AM

Answers

  • Hi Jak822,

    Thanks for your question.

    In this scenario, you can just create a simple count mesure called NumberInWeek as below:

    NumberInWeek := count('Transaction'[Date]) 

    Then drag Company to Rows, week of Year to Columns, Measure NumberInWeek to Values, see below image:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 13, 2017 2:20 AM

All replies

  • Hi Jak822,

    This is a Power Pivot Forum, so you should load your data into your Power Pivot Data model if you use Excel or Power BI data model if you use Power BI. I assume you have used Excel as you talked about Pivot Table. Once you have loaded your date table and transaction table, a relationship should automatically be created between your 2 tables based on date field. If not automatically created, you have to manually create it.

    Once all these done, create a pivot table, drag your week number from your date table to row field, add a calculated field with DAX code like this CustomerCount:=DISTINCTCOUNT(Transaction[Company]) and drag it to value area.

    Now you should have a simple report telling you how many companies you had for each week.

    Is this what you were trying to do?

    Thursday, October 12, 2017 11:09 AM
  • Hi Jak822,

    Thanks for your question.

    In this scenario, you can just create a simple count mesure called NumberInWeek as below:

    NumberInWeek := count('Transaction'[Date]) 

    Then drag Company to Rows, week of Year to Columns, Measure NumberInWeek to Values, see below image:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 13, 2017 2:20 AM