Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello !
I am currently working on a inventory dashboard. I have a transaction table (day, item, category, quantity) like this one
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)
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
How can I do to display the correct inventory for those days ?
Thanks for your help !
Solved! Go to 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
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 , Try like
cumulated_quantity =
CALCULATE(
SUM('transactions'[quantity]),
FILTER(
ALLSELECTED('transactions'),
'transactions'[day]>= max('transactions'[day])
&& 'transactions'[category]= max('transactions'[category])
)
)
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:
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |