Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering and programming x axis.

Hello,    I am attempting to convert my data report from Excel to Power BI,   I am trying to convert a column graph which shows the average calculation of the previous 3 years then the average ca...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous,

     

    To achieve the custom X-axis, you a custom table should be created. Before this, create a column to mark the date firstly.

     

    X-axis = 
var _currentY = YEAR(TODAY())
VAR _previoesY = _currentY-3
return
IF(YEAR([Date])=_currentY,FORMAT([Date],"mmm"),IF(YEAR([Date])>=_previoesY,FORMAT([Date],"\YE yyyy"),"Old data"))

     

     

    X =
VAR _A =
    ADDCOLUMNS (
        VALUES ( 'Table'[X-axis] ),
        "MAXDate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( 'Table', [X-axis] = EARLIER ( [X-axis] ) )
            )
    )
RETURN
    UNION (
        ADDCOLUMNS ( _A, "RANK", RANKX ( _A, [MAXDate],, ASC ) ),
        { ( "AVG " & YEAR ( TODAY () ), MAX ( 'Table'[Date] ), 16 ) }
    )

     

    X table looks like this:

     

    Second, create a inactive relationship between fact table and X table depends on x-axis.

    Finally, measure to calculate the average.

     

    Measure =
IF (
    SELECTEDVALUE ( 'X'[X-axis] )
        = "AVG " & YEAR ( TODAY () ),
    CALCULATE (
        AVERAGE ( 'Table'[Values] ),
        FILTER ( ALL ( 'Table'[Date] ), YEAR ( [Date] ) = YEAR ( TODAY () ) )
    ),
    CALCULATE (
        AVERAGE ( 'Table'[Values] ),
        USERELATIONSHIP ( 'X'[X-axis], 'Table'[X-axis] )
    )
)

     

    Result:

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    I forgot to remain you the relationship should be inactive.

    Best Regards

    Community Support Team _ chenwu zhu

     

    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
4 years ago

Hello,

 

Thank you for the help, it was extremely helpful. 

 

However, as I was trying to adapt your example to my situation I was not able to replicate the results.

 

I created the X costume table and the Table custom table, however Incorporated the Table costume table to my existing table Sheet2.

 

The problem is whenever I generate the stacked column chart it does not show the value for the AVG 2022 bar. 

 

I think the problem lies in the measure DAX created in the Table custom table.

 

In your example whenever you are calculating the value for AVG 2022 you do so by calculating the average of Values Column,

 

Measure =

IF(SELECTEDVALUE('X'[X-axis])="AVG "&YEAR(TODAY()),

CALCULATE(AVERAGE('Table'[Values]),FILTER(ALL('Table'[Date]),YEAR([Date])=YEAR(TODAY()))),

CALCULATE(AVERAGE('Table'[Values]),USERELATIONSHIP('X'[X-axis],'Table'[X-axis])))

 

However in my case I need to calculate my value from an existing Measure I created called Rejection Rate, below is my modified version .

 

Measure =

IF(SELECTEDVALUE('X'[X-axis])="AVG "&YEAR(TODAY()),

CALCULATE('Sheet2'[Rejection Rate],FILTER(ALL('Sheet2'[Date]),YEAR([Date])=YEAR(TODAY()))),

CALCULATE('Sheet2'[Rejection Rate],USERELATIONSHIP('X'[X-axis],'Sheet2'[X-axis])))

 

Instead of calling out the column [Values] I called out the Measure [Rejection Rate], it is odd because this yielded correct values for all the years and months except for AVG 2022.

 

Thank you again for your time,

 

Best regards,

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    I forgot to remain you the relationship should be inactive.

    Best Regards

    Community Support Team _ chenwu zhu

     

    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
      4 years ago

      That was it, thank you for your help. 

       

      How did making the relationship inactive solve the problem?