Forum Discussion

cheryl0316's avatar
cheryl0316
Helper II
1 year ago
Solved

AVERAGEX returns blank

This is a sample report regarding employee performance.

 

  • If an employee doesn't meet the sales target, he will receive a notice.
  • If an employee exceeds the maximum allowed refund amount, he will receive a notice.
  • If an employee doesn't meet both requirements, he will receive two notices.

 

The measure "Number_Notice" calculates the number of notices

 

I want to calculate the number of notices for each country, city, employee, and product category.

Then I need to create a bar chart to display the average number of notices grouped by country and city.

 

This is my measure

Average_Notice =
VAR a = SUMMARIZECOLUMNS(dimLocation[Country],dimLocation[City],dimEmployee[Employee Name],dimProduct[Product Category])
VAR b = ADDCOLUMNS(a,"n",CALCULATE([Number_Notice]),"sa",CALCULATE([Sum_Sales Amount]))
RETURN
AVERAGEX(filter(b,[sa]>0 && [n]=SELECTEDVALUE(Notice[Number])), [Number_Notice])
 
The bar chart shows nothing - Why? I guess there's something wrong with AVERAGEX
 

 

Any advice would be appreciated. Thank you so much!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi cheryl0316 

     

    Certainly!

     

    The SELECTEDVALUE() returns blank when the Slicer Number of Notice is all, because there are multipal values being selected. Please refere to this link:

    SELECTEDVALUE function - DAX | Microsoft Learn

     

    So change the measure this way:

    Average_Notice =
VAR a =
    SUMMARIZECOLUMNS (
        dimLocation[Country],
        dimLocation[City],
        dimEmployee[Employee Name],
        dimProduct[Product Category]
    )
VAR b =
    ADDCOLUMNS (
        a,
        "n", CALCULATE ( [Number_Notice] ),
        "sa", CALCULATE ( [Sum_Sales Amount] )
    )
RETURN
    IF (
        SELECTEDVALUE ( Notice[Number] ) <> BLANK (),
        AVERAGEX (
            FILTER ( b, [sa] > 0 && [n] = SELECTEDVALUE ( Notice[Number] ) ),
            [n]
        ),
        AVERAGEX ( FILTER ( b, [sa] > 0 ), [n] )
    )

     

    The result is as follow:

     

     

    Best Regards

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cheryl0316 

     

    Maybe you can try this:

     

    Average_Notice =
VAR a =
    SUMMARIZECOLUMNS (
        dimLocation[Country],
        dimLocation[City],
        dimEmployee[Employee Name],
        dimProduct[Product Category]
    )
VAR b =
    ADDCOLUMNS (
        a,
        "n", CALCULATE ( [Number_Notice] ),
        "sa", CALCULATE ( [Sum_Sales Amount] )
    )
RETURN
    AVERAGEX ( FILTER ( b, [sa] > 0 ), [n] )

     

    It seems that the [n]=SELECTEDVALUE(Notice[Number]) is the reason of the error. There aren't relationship between Notice table and dimLocation table so the SELECTEDVALUE(Notice[Number]) returns blank in the Clustered column chart so that the result is blank.

    The result is as follow:

    \

     

     

    Best Regards

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

    • cheryl0316's avatar
      cheryl0316
      Helper II
      1 year ago

      Hi Zhengdong,

       

      Thank you for your help. I added [n] = SELECTEDVALUE(Notice[Number]) because I want the bar chart to be filtered by the slicer "Number of Notice." Is it possible to make the bar chart interact with the slicer?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi cheryl0316 

         

        Certainly!

         

        The SELECTEDVALUE() returns blank when the Slicer Number of Notice is all, because there are multipal values being selected. Please refere to this link:

        SELECTEDVALUE function - DAX | Microsoft Learn

         

        So change the measure this way:

        Average_Notice =
VAR a =
    SUMMARIZECOLUMNS (
        dimLocation[Country],
        dimLocation[City],
        dimEmployee[Employee Name],
        dimProduct[Product Category]
    )
VAR b =
    ADDCOLUMNS (
        a,
        "n", CALCULATE ( [Number_Notice] ),
        "sa", CALCULATE ( [Sum_Sales Amount] )
    )
RETURN
    IF (
        SELECTEDVALUE ( Notice[Number] ) <> BLANK (),
        AVERAGEX (
            FILTER ( b, [sa] > 0 && [n] = SELECTEDVALUE ( Notice[Number] ) ),
            [n]
        ),
        AVERAGEX ( FILTER ( b, [sa] > 0 ), [n] )
    )

         

        The result is as follow:

         

         

        Best Regards

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