Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Advertiser Frequency - DAX help

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?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Managed to do it by

     

    Table 4 = SUMMARIZE(Query1,Query1[TrueCompany],"Number of Weeks",CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"),"Revenue",CALCULATE(sum(Query1[InsertNetCost]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"))
  • Omega's avatar
    Omega
    8 years ago

    Instead of putting multiple ORs (||), try 

     

    CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]), AND(right(Query1[FinYear/Month],2)< 54, left (Query1[FinYear/Month],4)< 2018)),Query1[ttlname] = "Ht"),"Revenue",

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Any ideas anyone?

     

    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.  The a count if in that row total column for 

     

    how many 1's twos etc.

     

    Must be an easier way?

    • Omega's avatar
      Omega
      Icon for Impactful Individual rankImpactful Individual
      8 years ago

      Can you share a screenshot of the pivot and the tables you have created?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Omega

         

        Pivot

         

         Calendar

         

         

        Sales Table

         

        Ad Table

         

        One of the ways I am thinking is if I put in the fin month/week into the Sales table then do a count on that summarized by company?

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    Please share the pbix file with sample data, I will test if it can be achieved.

     

    Notice: Do mask sensitive data before uploading.

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Anonymous

       

      Link below...

       

      https://drive.google.com/file/d/0B0ozcJ6ZI3zEM1NZWGhhcUgtbFU/view?usp=sharing

       

      So what I would be looking for is a table with 

       

      Number of Companies  Number of Weeks Advertised

      1                                     15

      1                                     8

       

      and the hopefuly be able to use code instead of the slicer to fit months....

       

      Thanks

       

      C

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Managed to do it by

         

        Table 4 = SUMMARIZE(Query1,Query1[TrueCompany],"Number of Weeks",CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"),"Revenue",CALCULATE(sum(Query1[InsertNetCost]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"))