Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Compare monthly average vs Total Average

Hi

 

I have an issue with a calculation and crating a measure

 

Tried diff formulas but not working so far. Any tip?

 

Now I want to create a difference between the monthly average vs Total (in this case 14.52)

 

July: 14.18 - 14.52

Aug: 14.81 - 14.52

etc...

 

 

Thanks,
 
Need Help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi romovaro ,

     

    Based on the sample and description you provided, Please try code as below.
    My Sample:

    Total Average =
VAR _Day =
    SUM ( 'LOA assignations'[LOA Sig date vs greenlight date] )
VAR CNT =
    COUNTROWS ( 'LOA assignations' )
RETURN
    _Day / CNT

    LOA Month Comp =
AVERAGE ( 'LOA assignations'[LOA Sig date vs greenlight date] )
    - SELECTEDVALUE ( 'LOA assignations'[Total Average] )

    Result is as below.

     

    If this reply still couldn't help resolve your issue, please feel free to ask us. Please share more details or share a sample pbix after removing sensitive data with us.

     

    Best Regards,
    Yulia Yan

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

    • romovaro's avatar
      romovaro
      Icon for Responsive Resident rankResponsive Resident
      2 years ago

      Hi Ahmed, I will try later. Cannot do it in my company due to system restrictions

       

      Avg Days is a Datediff formula to see the days btw the 2 dates.

      LOA Sig date vs greenlight date = DATEDIFF('LOA assignations'[LOA Signature date].[Date],'LOA assignations'[Greenlight Date LOA tracker].[Date],DAY)
       
      And I just  select the Average option
       

       

      Then I want to calculate the Average of this month - the Total Average (Average YTD)

      LOA Month Comp = CALCULATE(AVERAGE('LOA assignations'[LOA Sig date vs greenlight date])-14.52)
       
      I need to be able to create a formula to substitute the 14.52)
      Results inside green circle are the ones I need

       

       

      thanks,

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi romovaro ,

     

    Based on the sample and description you provided, Please try code as below.
    My Sample:

    Total Average =
VAR _Day =
    SUM ( 'LOA assignations'[LOA Sig date vs greenlight date] )
VAR CNT =
    COUNTROWS ( 'LOA assignations' )
RETURN
    _Day / CNT

    LOA Month Comp =
AVERAGE ( 'LOA assignations'[LOA Sig date vs greenlight date] )
    - SELECTEDVALUE ( 'LOA assignations'[Total Average] )

    Result is as below.

     

    If this reply still couldn't help resolve your issue, please feel free to ask us. Please share more details or share a sample pbix after removing sensitive data with us.

     

    Best Regards,
    Yulia Yan

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.