Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Target calculation based on percentage adjustment

Hello!

 

I need help with this problem:

 

I have a percentage adjustment target at the level of the month, but the granularity is at the level of the day in the table.
I need to calculate the quarterly adjusted target.


More exactly: I have these percentages for 3 months:

 

DATEPERCENTQUARTER
1/1/20220.5Q1
1/2/20220.5Q1
1/3/20220.5Q1
1/4/20220.5Q1
1/5/20220.5Q1
1/6/20220.5Q1
1/7/20220.5Q1
1/8/20220.5Q1
1/9/20220.5Q1
1/10/20220.5Q1
1/11/20220.5Q1
1/12/20220.5Q1
1/13/20220.5Q1
1/14/20220.5Q1
1/15/20220.5Q1
1/16/20220.5Q1
1/17/20220.5Q1
1/18/20220.5Q1
1/19/20220.5Q1
1/20/20220.5Q1
1/21/20220.5Q1
1/22/20220.5Q1
1/23/20220.5Q1
1/24/20220.5Q1
1/25/20220.5Q1
1/26/20220.5Q1
1/27/20220.5Q1
1/28/20220.5Q1
1/29/20220.5Q1
1/30/20220.5Q1
1/31/20220.5Q1
2/1/20221Q1
2/2/20221Q1
2/3/20221Q1
2/4/20221Q1
2/5/20221Q1
2/6/20221Q1
2/7/20221Q1
2/8/20221Q1
2/9/20221Q1
2/10/20221Q1
2/11/20221Q1
2/12/20221Q1
2/13/20221Q1
2/14/20221Q1
2/15/20221Q1
2/16/20221Q1
2/17/20221Q1
2/18/20221Q1
2/19/20221Q1
2/20/20221Q1
2/21/20221Q1
2/22/20221Q1
2/23/20221Q1
2/24/20221Q1
2/25/20221Q1
2/26/20221Q1
2/27/20221Q1
2/28/20221Q1
3/1/20221Q1
3/2/20221Q1
3/3/20221Q1
3/4/20221Q1
3/5/20221Q1
3/6/20221Q1
3/7/20221Q1
3/8/20221Q1
3/9/20221Q1
3/10/20221Q1
3/11/20221Q1
3/12/20221Q1
3/13/20221Q1
3/14/20221Q1
3/15/20221Q1
3/16/20221Q1
3/17/20221Q1
3/18/20221Q1
3/19/20221Q1
3/20/20221Q1
3/21/20221Q1
3/22/20221Q1
3/23/20221Q1
3/24/20221Q1
3/25/20221Q1
3/26/20221Q1
3/27/20221Q1
3/28/20221Q1
3/29/20221Q1
3/30/20221Q1
3/31/20221Q1

 

I should calculate the maximum percentage at the quarterly level:

And in the end I should multiply this percentage by the monthly TARGET:

 

Expected value = 2500

 

 

How i can do this measure in dax?

 

Thank you! ~~

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    First you need to create a calculated column.

     

    Month = FORMAT('Table'[DATE],"mmm")

     

    Relationship.

    Then create a new measure.

     

    TARGET_AJUSTED = 
SUMX (
    SUMMARIZE (
        'TARGET',
        'TARGET'[MONTH],
        'TARGET'[TARGET],
        "max_of_percent", MAX ( 'Table'[PERCENT] )
    ),
    'TARGET'[TARGET] * [max_of_percent]
)

     

    Attached PBIX file for reference.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    First you need to create a calculated column.

     

    Month = FORMAT('Table'[DATE],"mmm")

     

    Relationship.

    Then create a new measure.

     

    TARGET_AJUSTED = 
SUMX (
    SUMMARIZE (
        'TARGET',
        'TARGET'[MONTH],
        'TARGET'[TARGET],
        "max_of_percent", MAX ( 'Table'[PERCENT] )
    ),
    'TARGET'[TARGET] * [max_of_percent]
)

     

    Attached PBIX file for reference.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data