Forum Discussion

SemaD_'s avatar
SemaD_
Regular Visitor
2 years ago

Help with DAX: Handling Dates without data in Average DAX

I really need help handling my dax. I am attempting to calculate the average number of unique sessions per month in a dynamic graph visual. I first start with a helper measure that helps me to determine the unique number of sessions per id #:

SDR ID average per CP ID =
AVERAGEX(
    All('Pogo E Connect Data'[CP ID]),
    CALCULATE(COUNTA('Pogo E Connect Data'[SDR ID])))

I then calculate the average with the help of a datetable like so: 
Average =    
AVERAGEX(
        VALUES('Calendar'[Date]),
            [SDR ID average per CP ID]
        )

The trouble I have is when I filter down by the ID (CPID), some IDs do not have sessions everyday. So lets say i select ID 1, that only had 1 session in May. In the graph where i added the Average dax, May shows an average number of sessions 1. Even though the average should be 1/31 = 0.032. So the average is massively overinflated for certain IDs. 

Does anyone know a way to handle this error. I have tried so many things such as returning blank dates as 0 however as long as the month has 1 session it will always show me an average of 1. Please help!
Need Help
Show and Tell
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi SemaD_ 

    You use the all() function, it will not be affected by the filter, please try the following measure.

     

    SDR ID average per CP ID =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Pogo E Connect Data' ),
        [CP ID] IN VALUES ( 'Pogo E Connect Data'[CP ID] )
    ),
    CALCULATE ( COUNTA ( 'Pogo E Connect Data'[SDR ID] ) )
)

     

    Best Regards!

    Yolo Zhu

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