Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am new to using PowerBI for a few weeks.
My system export data that are looking as the following:
Category | Product | SubProduct | Type | Qty | Week |
CategoryA | Product1 | SubProduct1a | Planned | 100.00 | 1 |
CategoryA | Product1 | SubProduct1a | Request | 150.00 | 1 |
CategoryA | Product1 | SubProduct1a | Delta | -50.00 | 1 |
CategoryA | Product1 | SubProduct1b | Planned | 200.00 | 1 |
CategoryA | Product1 | SubProduct1b | Request | 150.00 | 1 |
CategoryA | Product1 | SubProduct1b | Delta | 50.00 | 1 |
CategoryA | Product2 | SubProduct2a | Planned | 300.00 | 1 |
CategoryA | Product2 | SubProduct2a | Request | 100.00 | 1 |
CategoryA | Product2 | SubProduct2a | Delta | 200.00 | 1 |
CategoryA | Product2 | SubProduct2b | Planned | 500.00 | 1 |
CategoryA | Product2 | SubProduct2b | Request | 50.00 | 1 |
CategoryA | Product2 | SubProduct2b | Delta | 450.00 | 1 |
CategoryA | Product1 | SubProduct1a | Planned | 200.00 | 2 |
CategoryA | Product1 | SubProduct1a | Request | 150.00 | 2 |
CategoryA | Product1 | SubProduct1a | Delta | 50.00 | 2 |
CategoryA | Product1 | SubProduct1b | Planned | 60.00 | 2 |
CategoryA | Product1 | SubProduct1b | Request | 20.00 | 2 |
CategoryA | Product1 | SubProduct1b | Delta | 40.00 | 2 |
CategoryA | Product2 | SubProduct2a | Planned | 500.00 | 2 |
CategoryA | Product2 | SubProduct2a | Request | 50.00 | 2 |
CategoryA | Product2 | SubProduct2a | Delta | 450.00 | 2 |
CategoryA | Product2 | SubProduct2b | Planned | 40.00 | 2 |
CategoryA | Product2 | SubProduct2b | Request | 400.00 | 2 |
CategoryA | Product2 | SubProduct2b | Delta | -360.00 | 2 |
CategoryA | Product1 | SubProduct1a | Request | 250.00 | 3 |
CategoryA | Product1 | SubProduct1b | Planned | 300.00 | 3 |
CategoryA | Product1 | SubProduct1b | Delta | -50.00 | 3 |
CategoryA | Product2 | SubProduct2a | Planned | 280.00 | 3 |
CategoryA | Product2 | SubProduct2a | Request | 680.00 | 3 |
CategoryA | Product2 | SubProduct2a | Delta | -400.00 | 3 |
CategoryA | Product2 | SubProduct2b | Planned | 300.00 | 3 |
CategoryA | Product2 | SubProduct2b | Request | 120.00 | 3 |
CategoryA | Product2 | SubProduct2b | Delta | 180 | 3 |
I am working on a report in which I am displaying in a matrix Category, Product, and Qty
Product / Weeks | 1 | 2 | 3 |
ProductA | |||
Delta | 0 | 90 | -50 |
Planned | 300 | 260 | 300 |
Request | 300 | 170 | 250 |
ProductB | |||
Delta | 650 | 90 | -220 |
Planned | 800 | 540 | 580 |
Request | 150 | 450 | 800 |
My issue is that I would like the delta to be running from one week to another. My report is for supply chain usage and I use it to check the impact of variation on coverage. So I would like the matrix to display the following information instead and avoid the addition of an extra column by computing an extra measure.
Product / Weeks | 1 | 2 | 3 |
ProductA | |||
Delta | 0 | 90 | 40 |
Planned | 300 | 260 | 300 |
Request | 300 | 170 | 250 |
ProductB | |||
Delta | 650 | 740 | 520 |
Planned | 800 | 540 | 580 |
Request | 150 | 450 | 800 |
So I worked on creating a new column in my data computing the running delta using the following dax
RunningDelta =
IF(OR('Product'[Type] = "Planned", 'Product'[Type] = "Request"), BLANK(),
CALCULATE(
SUM('Product'[Qty]),
'Product'[Type] = "Delta",
ALLEXCEPT('Product', 'Product'[Product]),
'Product'[Index] <= EARLIER('Product'[Index])
)
)
RDelta = CALCULATE(
VALUES('Product'[RunningDelta]),
FILTER(ALL('Product'[Index]), 'Product'[Index] = MAX('Product'[Index]))
)
Solved! Go to Solution.
You need 3 measures. The ones with an _ (underscore) are auxiliary and should be hidden.
_TotalQty = Sum( T[Qty] )
_Delta =
IF( ISINSCOPE( T[Type] ) && SELECTEDVALUE( T[Type] ) = "Delta",
var MaxVisibleWeek = MAX( T[Week] )
var Result =
CALCULATE(
[_TotalQty],
T[Week] <= MaxVisibleWeek
)
return
Result
)
Total Qty =
IF ( HASONEFILTER( T[Type] ),
var TheType = SELECTEDVALUE( T[Type] )
var Result =
SWITCH( TRUE(),
TheType in {"Delta"}, [_Delta],
[_TotalQty]
)
return
Result
)
By the way, T[Week] must be an int (not text).
Thank you very much. I will now take the time to understand the code in the measures.
Here the pbix file https://filebin.net/88z55lm68jjb8gad/RunningDelta.zip?t=krutmvl5
You need 3 measures. The ones with an _ (underscore) are auxiliary and should be hidden.
_TotalQty = Sum( T[Qty] )
_Delta =
IF( ISINSCOPE( T[Type] ) && SELECTEDVALUE( T[Type] ) = "Delta",
var MaxVisibleWeek = MAX( T[Week] )
var Result =
CALCULATE(
[_TotalQty],
T[Week] <= MaxVisibleWeek
)
return
Result
)
Total Qty =
IF ( HASONEFILTER( T[Type] ),
var TheType = SELECTEDVALUE( T[Type] )
var Result =
SWITCH( TRUE(),
TheType in {"Delta"}, [_Delta],
[_TotalQty]
)
return
Result
)
By the way, T[Week] must be an int (not text).
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |