Measure not showing the Total

[utsavlexmark]

Hello,

I am working on a Power BI dashboard with lots of conditional values to be shown. In the process I found a obsrtuction and need help to overcome that. I have created one measure as follows:

QTY Need v1 = CALCULATE('analytics nabsi'[Demand]-'analytics nabsi'[Total AVAIL INVENTORY])

Now I need to create another measure on the basis of the result of this one. The condition is "If the Value of QTY Need v1 is less than 0 then the value will be 0 otherwise it will show the value of QTY Need v1". The Measure I created for this is as follows:

QTY Need = IF([QTY Need v1]<0,0,[QTY Need v1])

Now the problem is the Total is showing 0. I can identify the issue why it is showing 0. The measure QTY Need v1 has some negetive values and due to that the Total can be in negetive figure. As per the logic in Measure QTY Need that negetive value turns into 0.

Need help on from the forum.

Would like to mention one things - what ever value I am working with related to different products and those can be identified by a column "Partnumber".

Hope I am clear with my issue - if not please let me know what else you want to to know.

[v-juanli-msft]

Hi @utsavlexmark 

Create a measure

QTY Need =
IF (
    HASONEVALUE ( 'Table'[partnumber] ),
    IF ( [QTY Need v1] < 0, 0, [QTY Need v1] ),
    SUMX ( ALL ( 'Table'[partnumber] ), [QTY Need v1] )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[utsavlexmark]

Hello,

Thanks for the reply, but the total is in negetive figure. I want the total should be showing the sum of positive values only. 

As per the logic - the values of "QTY Need" are either 0 (negetive values will be shown as 0) or positive values. I want the sum of those positive values only.

Regards

Utsav

[v-juanli-msft · Accepted solution]

Hi @utsavlexmark 

Cretae a measure

QTY Need 2 =
IF (
    HASONEVALUE ( 'Table'[partnumber] ),
    IF ( [QTY Need v1] < 0, 0, [QTY Need v1] ),
    SUMX ( ALL ( 'Table'[partnumber] ), [QTY Need] )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[utsavlexmark]

Although little complected - but still acceptable as it is solving my problem. At least it will give me a time to find any other possible solution. Thank you very much @v-juanli-msft. Regards Utsav

[mohittimpus]

Hii @utsavlexmark, 

QTY Need v1 = CALCULATE('analytics nabsi'[Demand]-'analytics nabsi'[Total AVAIL INVENTORY])

For the above calculation,

Are you using per row calculation? If yes, then you need to create a calculated column in table:  

QTY Need v1= 'analytics nabsi'[Demand]-'analytics nabsi'[Total AVAIL INVENTORY]

Then you can create a measure:

QTY Need = IF(sum([QTY Need v1])<0,0,sum([QTY Need v1]))

If this doesn't work then can you please share sample data, so I can help you.

If it helps, then please mark it as Accept as Solution

Thanks,

mohittimpus 

[utsavlexmark]

Hello,

This is not working; you are right that the calculation I am using per row calculation – but few things I forgot to mention:

'analytics nabsi'[Demand] and analytics nabsi'[Total AVAIL INVENTORY] both are Measures.

Demand = SUMX('analytics nabsi','analytics nabsi'[AWS]*4+'analytics nabsi'[backordered])

analytics nabsi'[AWS]=Measure

analytics nabsi'[backordered]=General Column

Total AVAIL INVENTORY = SUM('analytics nabsi'[availableinventory])

analytics nabsi'[availableinventory]=General Column

The sample data is

Please let me know if you need any more information.

I hope that I didn’t made the things complicated.