Forum Discussion

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

Dont Bring Blank

Hi All!

 

i have this Measure:

Net USD AVG =
CALCULATE(
DIVIDE( SUM( 'DB 2022'[Net USD] ), COUNTROWS( 'Date' ) ),
keepfilters( 'Date'[Date] < TODAY()))
 

My data does not always reach the present day
Sometimes the current day is the 18th of the month and the data is only updated until the 12th of the month.What happens is that the formula divides the money by 18 instead of 12 and that's how I get a wrong average

Is it possible to add to the Measure that will not Bring in empty cells mean Blank?

 

Then no matter what day I am in the month it will only count up to where the data is

 

Thanks!

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    This is a better measure replacing the >0

    Net USD AVG = 

VAR NumDays=
    CALCULATE (
        DISTINCTCOUNT ('DB 2022'[Date] ),
        KEEPFILTERS( 'Date'[Date] < TODAY() ),
        NOT ISBLANK ( 'DB 2022'[Net USD] ),
        REMOVEFILTERS ( 'SORT GL' )
    )

VAR SumNet =
    CALCULATE (
        SUM ( 'DB 2022'[Net USD] ),
        KEEPFILTERS( 'Date'[Date] < TODAY() )
        )

RETURN DIVIDE (SumNet, NumDays)

     

    In your 'DB 2022' table you have rows for every date whether there is a value for [Net USD] or not. My original measure went for >0 just to make sure it was picking days with values. The above version is better in case you did get negative values.

     

    The reason it wasn't working when the sort field is added in is there are days in each month when there are no values for that particular sort. It was therefore not including that day in the division for that section. By using the REMOVEFILTERS ( 'Sort GL' ) it says no matter what sort is selected divided by the total number of days in the whole period.

14 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Would this work: (Hard to test without a demo file)

    Net USD AVG =
CALCULATE(
	DIVIDE( 
		SUM( 'DB 2022'[Net USD] ), 
		DISTINCTCOUNT( 'DB 2022'[Date] ) 
	),
	KEEPFILTERS( 'Date'[Date] < TODAY() )
)


    Replace 'DB 2022'[Date] with the date column on your table.

    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Or depending on how big your fact table is this might be faster:

       

       

      Net USD AVG =
CALCULATE(
	AVERAGEX (
		VALUES ( 'Date'[Date] ),
		CALCULATE ( SUM ( 'DB 2022'[Net USD] ) )
	),
	KEEPFILTERS( 'Date'[Date] < TODAY() )
)

       

       

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

        bcdobbs 

        This formula works great and I have tried it already,
        My problem is in AverageX that sometimes I only get a large amount one day in a month and I still divide it by 31 which is why I used Divide

         

        Thank you so much for the quick responses!
        It's really urgent for me

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

      Hi bcdobbs 

      It helped a little,
      It is still split in 19 days even though the data is up to the 17th

      Unfortunately I can not share ...

      • bcdobbs's avatar
        bcdobbs
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Are you able to share some dummy data in the same table structure. Can't quite picture why it doesn't work.


        Feel I'm missing something I think averagex and what you're trying to do will do the same thing.

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

     

    The following two measures both give the desired 6076K.

     

     

     

    Net USD AVG = 

VAR NumDays=
    CALCULATE (
        DISTINCTCOUNT ('DB 2022'[Date] ),
        KEEPFILTERS( 'Date'[Date] < TODAY() ),
        'DB 2022'[Net USD] > 0
    )

VAR SumNet =
    CALCULATE (
        SUM ( 'DB 2022'[Net USD] ),
        KEEPFILTERS( 'Date'[Date] < TODAY() )
        )

RETURN DIVIDE (SumNet, NumDays)

     

     

     

    My prefered way is to go with the averagex. Yours wasn't working because you'd gotten rid of the outer calculate. With the demo data you sent both give the same values for each year:

     

     

     

    Net USD AVG 2 = 
CALCULATE (
    AVERAGEX(
        VALUES( 'Date'[Date] ), 
        CALCULATE( SUM( 'DB 2022'[Net USD] ) ) 
    ),
    KEEPFILTERS( 'Date'[Date] < TODAY() ) 
)

     

     

     
    You need the outer calculate to remove future dates from the filter context. The inner calculate then causes context transition to happen so you get the SUM for each day.

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

      bcdobbs 

      First thanks for your time

      But both do not work
      If you go to 2021 and switch between the two Measure (NET USD AVG, AND NET USD AVG 2)
      You will see that Other for FS is a large amount at the monthly level
      That's why I used Divide

      • bcdobbs's avatar
        bcdobbs
        Icon for Community Champion rankCommunity Champion
        4 years ago

        So are you happy that the division version gives the right values? If so that's great.

         

        I'm just really confused because on mine both give the same values: