Cumulative measure and empty legend in barchart

[phaidara]

Hello !

I am currently working on a inventory dashboard. I have a transaction table (day, item, category, quantity) like this one

Inventory table

And I would like to display the inventory value per category for each day.

I have created a cumulated measure to compute the total inventory per day:

cumulated_quantity = 
CALCULATE(
	SUM('transactions'[quantity]),
	FILTER(
		ALLSELECTED('transactions'[day]),
		ISONORAFTER('transactions'[day], MAX('transactions'[day]), DESC)
	)
)

When I display the full inventory per day I see the expected result (day 1: 27, day 2: 24, day 3: 16)

Correct cumulated inventory

But when I apply the category as a legend, I notice that if there is no transaction for a given day and category, the category is not displayed in the chart:

day 1: 27 , day 2: 12, day 3: 4

Missing categories in inventory (days 2 and 3)

How can I do to display the correct inventory for those days ?

Thanks for your help !

[Anonymous · Accepted solution]

Hi @phaidara ,

You will need to create an extra category table with no relationship with transactions table.

category = DISTINCT(transactions[category])

Then use below formula to get the result.

Measure = CALCULATE(SUM(transactions[quantity]),FILTER(ALLSELECTED(transactions),transactions[day]<=MAX(transactions[day])&&transactions[category]=SELECTEDVALUE('category'[category])))

 

Best Regards,

Jay

[phaidara]

Hey @Anonymous !

Thanks a lot, it is exactly what I was looking for !

[amitchandak]

@phaidara , Try like

cumulated_quantity =
CALCULATE(
SUM('transactions'[quantity]),
FILTER(
ALLSELECTED('transactions'),
'transactions'[day]>= max('transactions'[day])
&& 'transactions'[category]= max('transactions'[category])
)
)

[phaidara]

Hi @amitchandak !

Thank you for your quick reply ! 

I've tried your solution but it does not seem to work.

Here is the new result that i got when i copied: