Forum Discussion

cheryl0316's avatar
cheryl0316
Icon for Helper II rankHelper 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...
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.

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
    Icon for Helper II rankHelper 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.