Forum Discussion

Br1-981's avatar
Br1-981
Helper I
2 years ago
Solved

Empty values in matrix

Hi,

I have a data model like this:

 

I need to build a matrix:

Using YEAR from Calendar

Media from DIS_Media

and SPEND ACTUAL FROM Weekly Data 

it is working properly.

If I add a Calculated Measurement to show for each row/Year the difference against the previous year:

ActualSpend_EUR_PY = IF(Weekly_Calc[ActualSpend_EUR]>0,
    CALCULATE (
    Weekly_Calc[ActualSpend_EUR],
    OFFSET (
        -1,
        ALL ( 'Calendar'[Year] ),
        ORDERBY ( 'Calendar'[Year], ASC )
    )
),0)

then:

ActualSpend_EUR_VarPY = IF([ActualSpend_EUR_PY]>0,(DIVIDE(Weekly_Calc[ActualSpend_EUR], Weekly_Calc[ActualSpend_EUR_PY])-1),0)

 

The matrix is reporting all the years from the table Calendar:

 

While I would show just years where SPEND ACTUAL is more than 0, I tried using filters applied to the specific widgets.

 

Any Idea?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Br1-981 

     

    Try the following dax 

    ActualSpend_EUR_PY = 
CALCULATE(
    [ActualSpend_EUR], 
    DATEADD('Calendar'[Date], -1, YEAR)
)

    ActualSpend_EUR_VarPY = 
IF(
    [ActualSpend_EUR_PY] > 0,
    ([ActualSpend_EUR] - [ActualSpend_EUR_PY]) / [ActualSpend_EUR_PY],
    BLANK()
)

     

     

     

    Finally, to ensure that the matrix only shows years where "SPEND ACTUAL" is more than 0, you can use a visual level filter on your matrix. Here's how to apply the filter:

    1. Click on your matrix visual to select it.
    2. Go to the "Filters" pane on the right side of the Power BI Desktop interface.
    3. Under the "Visual level filters" section, find the "SPEND ACTUAL" measure.
    4. Set the filter to show only values greater than 0.

     

    If I misunderstood what you meant. please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

     

     

     

     

    Best Regards,

    Jayleny

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Br1-981 

     

    Try the following dax 

    ActualSpend_EUR_PY = 
CALCULATE(
    [ActualSpend_EUR], 
    DATEADD('Calendar'[Date], -1, YEAR)
)

    ActualSpend_EUR_VarPY = 
IF(
    [ActualSpend_EUR_PY] > 0,
    ([ActualSpend_EUR] - [ActualSpend_EUR_PY]) / [ActualSpend_EUR_PY],
    BLANK()
)

     

     

     

    Finally, to ensure that the matrix only shows years where "SPEND ACTUAL" is more than 0, you can use a visual level filter on your matrix. Here's how to apply the filter:

    1. Click on your matrix visual to select it.
    2. Go to the "Filters" pane on the right side of the Power BI Desktop interface.
    3. Under the "Visual level filters" section, find the "SPEND ACTUAL" measure.
    4. Set the filter to show only values greater than 0.

     

    If I misunderstood what you meant. please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

     

     

     

     

    Best Regards,

    Jayleny

     

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

    • Br1-981's avatar
      Br1-981
      Helper I
      2 years ago

      Thanks a lot for your help, now the measurement part is working well.

      The only problem is with the last step because It is showing values also for 2025 (where I have no actual spend) just because It is calculating the PY one, even filtering at visual level Spend>0 i got:

      So I modified a bit the PY calculation:

      ActualSpend_EUR_PY= IF([ActualSpend_EUR]>0,CALCULATE(
    [ActualSpend_EUR], 
    DATEADD('Calendar'[Date], -1, YEAR)
),BLANK())

      and now it is working 😄 (no filtering at visual level needed):