Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Power BI issue on Matrix Visual

Hi,

I have some trouble with the Matrix Visual in PowerBI, hope that somebody could help me out:

 

I need to show the sum for my invoices, therefore I created my rows in hierarchical order (like Company Code, Account, Period, Invoice Number). On values (shown as column), I have put my sum field of currency amount which is already stored in my table. So far, so good.

 

But now, I would like to show an additional information like "positive" or "negative" if the period isn't balanced (in a separate column related to my existing sum field). So I tried to insert another value (also as column) based on a measure. In this measure, I tried to "calculate" the correct value but I sadly failed 😕

 

The result should look like this:

 

In my measure, I tried it like this:

SelectedStatus =

var selectedstatus =

SELECTEDVALUE('Table'[amount])

return if (selectedstatus > 0, "positive", "negative")

 

Now my questions:

1. Is a measure with SELECTEDVALUE the correct way to reach the goal?

2. Is anybody of you able to help me out or provide me the correct DAX query?

 

Thanks & kind regards!

Tommi

1 Reply

  • Burningsuit's avatar
    Burningsuit
    Resident Rockstar
    4 years ago

    Hi Anonymous 

    In this case you don't need SELECTEDVALUE because the matrix supplies the filter context.

    Create a measure:

    SelectedStatus = IF( 'Table'[amount]> 0, "positive", "negative")

    and put that on the Matrix.

    You may need to set a visual level filter "show items when the value is not blank" if you're getting blank items.

    You also may need to consider what you want if the 'Table'[amount] = 0. At the moment with my code, following your example it will show "negative" for Zero entries. You may want a measure like this...

    SelectedStatus = IF( 'Table'[amount]> 0, "positive",  IF( 'Table'[amount]= 0, "zero", "negative"))

    to show zero separately.

    Hope this helps

    Stuart