Forum Discussion

netanel's avatar
netanel
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Gross + if

Hi All!

 

I have a formula that I can not put together correctly

This is the first part of the formula that works great for me:

Gross USD average per Day =

CALCULATE(

    DIVIDE( SUM( 'DB 2021'[Gross USD] ), COUNTROWS( 'Date' ) ),

   keepfilters(  'Date'[Date] < TODAY())

)

 
Now I try to connect this condition:
If the Source = "Amn" then bring me both Gross USD and Fee
Otherwise just bring me Gross USD
 
Need Help
  • netanel's avatar
    netanel
    4 years ago

    Thank you all!
    This is the correct formula:

    Gross USD =
    IF (
    SELECTEDVALUE ( 'db 2021'[Source] ) = "Amazon",
    CALCULATE (
    DIVIDE (
    ( SUM ( 'DB 2021'[Gross USD] ) + SUM ( 'DB 2021'[Fee USD] ) ),
    COUNTROWS ( 'Date' )
    ),
    KEEPFILTERS ( 'Date'[Date] < TODAY () )
    ),
    CALCULATE (
    DIVIDE ( SUM ( 'DB 2021'[Gross USD] ), COUNTROWS ( 'Date' ) ),
    KEEPFILTERS ( 'Date'[Date] < TODAY () )
    )
    )

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    4 years ago

    netanel  Try this measure:

    MEASURE = IF(MAX(Table[Source])="Amn",SUM(Table[Fee])+SUM(Table[Gross USD]),SUM(Table[Gross USD]))

     

    • netanel's avatar
      netanel
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Hi Tahreem24 

      Thanks for the response,

      this is also the direction I go
      But he is without average
      Take a look at my original formula for calculating the Gross

      Gross USD average per Day =

      CALCULATE(

          DIVIDE( SUM( 'DB 2021'[Gross USD] ), COUNTROWS( 'Date' ) ),

         keepfilters(  'Date'[Date] < TODAY())

      )

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    netanel , not sure how that is related to avg gross

    You need to have new fee measure

    Fee m = calculate(sum('Revenue DB'[Fee]) , filter('Revenue DB', Source <> "Amn" ))

     

    This will not show Fee for Source = "Amn"

     

  • netanel's avatar
    netanel
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    Thank you all!
    This is the correct formula:

    Gross USD =
    IF (
    SELECTEDVALUE ( 'db 2021'[Source] ) = "Amazon",
    CALCULATE (
    DIVIDE (
    ( SUM ( 'DB 2021'[Gross USD] ) + SUM ( 'DB 2021'[Fee USD] ) ),
    COUNTROWS ( 'Date' )
    ),
    KEEPFILTERS ( 'Date'[Date] < TODAY () )
    ),
    CALCULATE (
    DIVIDE ( SUM ( 'DB 2021'[Gross USD] ), COUNTROWS ( 'Date' ) ),
    KEEPFILTERS ( 'Date'[Date] < TODAY () )
    )
    )