Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello, I’m new to DAX and I’m trying to get a Running Total (Supply and Demand) based on the column SKU Index:
What I’m trying to do is to replicate the next formula:
I'm doing this inside Excel, first I created that table using Power Query, and now, the last step is to create that Measure with DAX but I don't know how.
The table with the real data is much larger, it comes from SAP and it has 450K registers. I already uploaded that table to the data model and then 2 pivot tables will be created from that.
The only missing thing is this running total measure based on the SKU Index, any help would be greatly appreciated, thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
Did you solve your problem, if yes, please mark it as a solution, if not, please try these codes below to do that.
My table looks like:
New column to calculate:
Column =
SUMX (
FILTER (
'Table',
[SKU] = EARLIER ( 'Table'[SKU] )
&& [SKU Index] <= EARLIER ( 'Table'[SKU Index] )
),
[+] - [-]
)
Result:
Measure:
Measure =
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
[SKU] = SELECTEDVALUE ( 'Table'[SKU] )
&& [SKU Index] <= SELECTEDVALUE ( 'Table'[SKU Index] )
),
[+] - [-]
)
Result:
Best regards.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , I think you need sub category index
https://www.youtube.com/watch?v=7ypdbuCeBtk
https://www.youtube.com/watch?v=7CqXdSEN2k4
Hi! Yes, sorry I forgot to mention that, but it is the last column called SKU index.
I actually used the first video you suggested from Goodly to create it, and now with that sub category index, I think it is feasible to perform a running total based on that column.
Hi @Anonymous ,
Did you solve your problem, if yes, please mark it as a solution, if not, please try these codes below to do that.
My table looks like:
New column to calculate:
Column =
SUMX (
FILTER (
'Table',
[SKU] = EARLIER ( 'Table'[SKU] )
&& [SKU Index] <= EARLIER ( 'Table'[SKU Index] )
),
[+] - [-]
)
Result:
Measure:
Measure =
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
[SKU] = SELECTEDVALUE ( 'Table'[SKU] )
&& [SKU Index] <= SELECTEDVALUE ( 'Table'[SKU Index] )
),
[+] - [-]
)
Result:
Best regards.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is indeed really helpful and works exactly as expected, thanks a lot
User | Count |
---|---|
105 | |
69 | |
48 | |
47 | |
47 |