Forum Discussion

Br1-981's avatar
Br1-981
Icon for Helper I rankHelper 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 ...
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.

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
Icon for Helper I rankHelper 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):