Forum Discussion

vivran22's avatar
vivran22
Community Champion
5 years ago

Target Cumulation

Hey community memebers,

 

Need help in writing a DAX measure for this scenario:

 

I have the following target file:

 

Date Campaign (New Nomenclature) FTE Conversion
1-Sep-20 C_Rev_YHRT_App User Campaign 6 0.135
1-Sep-20 C_Rev_YHRT_ABG Devotion- IBADAT 3 0.135
1-Sep-20 C_Rev_YHRT_Fitness 7 0.135
1-Sep-20 C_Rev_YHRT_Comedy 7 0.135
1-Sep-20 C_Rev_YHRT_ABG English 4 0.135
1-Sep-20 C_Rev_YHRT_ABG Bollywood 14 0.135
1-Sep-20 C_Rev_YHRT_ABG Seniors 15 0.135
1-Sep-20 C_Rev_YHRT_Hollywood Local ROI 12 0.135

 

Link to the file

 

which is related to other support tables:

 

There are multiple level of grouping:
Fields present in campaign table: LOB, Sub Campaign, Campaign.
Fields present in Date table: Day Group (group of 10 days, 1-10, 11-20, 20-30, so on and so forth), Date

 

The lowest level is Campaign.

 

Depending on the filters applied, I want to sum the FTE target after taking an average at the campaign level.


For example, if I select the day group G1 (Sep 1 - 10), then it should take an average of FTE target for each campaign during this period, then return the sum.

 

Similarly, if I want to see FTE target at the LOB level for a day group, then it should take average of the campaign then return the sum.

 

Any help will be appreciated.

 

Thanks!

Vivek

 

3 Replies

  • DavisBI's avatar
    DavisBI
    Solution Specialist
    5 years ago

    Hi, vivran22 

     

    I don't know if my understanding is right.

    If I select Day Group (Jan 1 to Jan 10), is that should be calculated as the figure below?

     

    Best!

    • vivran22's avatar
      vivran22
      Community Champion
      5 years ago

      DavisBI 

       

      Thank you for your response!

       

      Following example may help you understand the scenario better:

       

      When summerizing data at Day group level:

      1. Average for each day in the group by campaign
      2. Sum the overall average for each campaign (36 + 112)

       

      When added another level to the filter:

      1. Calcualte the average at the campaign level
      2. Sum the average at camapign level for LOB and then roll it up to Group

       

      Hope this helps.

       

      Thanks!
      Vivek

       

  • tex628's avatar
    tex628
    Community Champion
    5 years ago

    If we start with the campaign based calculation, it sounds like it can be done with a summarize/sumx calculation. 

    Measure = 
SUMX(
SUMMARIZE(
ftCampaign[Campaign]
"Value", AVERAGE( dtTargets[FTE] )
) , 
[Value]
)


    How does this look? 

    Br,
    J