Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi There,
I'll really appreciate somone's urgent support.
I've gone through multiple Running Total examples, however, I am not sure if it's helping me out. I'll appreciate someone's help.
So I've the below table summary from the main fact table, I've.
POBU | Process Flow | If Following the Process Flow | If POBU Has Started Following the Process Flow | PO Create Date - Date | (Result) Total POBUs Started |
A | 0 | No | Started | 3/16/2017 0:00 | 0 |
B | 0 | No | Started | 3/17/2017 0:00 | 0 |
C | 0 | No | Started | 3/18/2017 0:00 | 0 |
A | 1 | Yes | Started | 3/19/2017 0:00 | 1 |
C | 1 | Yes | Started | 3/20/2017 0:00 | 2 |
D | 1 | Yes | Started | 3/21/2017 0:00 | 3 |
B | 1 | Yes | Started | 3/22/2017 0:00 | 4 |
A | 1 | Yes | Started | 3/23/2017 0:00 | 4 |
B | 1 | Yes | Started | 3/24/2017 0:00 | 4 |
E | 1 | Yes | Started | 3/24/2017 0:00 | 5 |
F | 0 | No | Not Started | 3/24/2017 0:00 | 5 |
Here, The First Column has the Operating Units Codes,
Second Column Shows the Code of Process Flow i.e. 1 - Pass, 0 - Fail.
Column 3 Tells, if Column 2 was a Pass or not, on each date, the transaction was performed.
Column 4 tells, if the respective Operating Unit has started following the process or not .
Column 5 has the transaction date.
and Last Column is the result I am looking for. i.e. Total POBUs started. I need to make a running total unit chart, such that I can show the performance by each month.
Looking forward for someone's help.
Rgds,
Vikrant Singh
Solved! Go to Solution.
I've came across another method.
Get the first date of the POBU with first True case. i.e. Process Flow = 1
First Date = CALCULATE
(MIN(Table[PO Create Date - Date].[Date]),
FILTER(ALL('Standard Naming Detail - All - Metrics - V1'),Table[POBU]=EARLIER('Table'[POBU]) && Table [Process Flow]="1")
Once the First Date Column is created, get the Cummulative Total using the below DAX:
Pareto Total Per Month =
VAR
CurrentTotal = 'Table'[First Date]
Return
SUMX(FILTER('Table','Table'[First Date]<=CurrentTotal),'Table'[Process Flow])
I've came across another method.
Get the first date of the POBU with first True case. i.e. Process Flow = 1
First Date = CALCULATE
(MIN(Table[PO Create Date - Date].[Date]),
FILTER(ALL('Standard Naming Detail - All - Metrics - V1'),Table[POBU]=EARLIER('Table'[POBU]) && Table [Process Flow]="1")
Once the First Date Column is created, get the Cummulative Total using the below DAX:
Pareto Total Per Month =
VAR
CurrentTotal = 'Table'[First Date]
Return
SUMX(FILTER('Table','Table'[First Date]<=CurrentTotal),'Table'[Process Flow])
Hi @singhv2
Create an index column in query editor from 1
Create two calculated columns
new flow = VAR flag = IF ( [Process Flow] = 0, BLANK (), CALCULATE ( SUM ( 'table'[Process Flow] ), FILTER ( ALLEXCEPT ( 'table', 'table'[POBU] ), [Index] <= EARLIER ( [Index] ) ) ) ) RETURN IF ( flag > [Process Flow], 0, [Process Flow] )
total = CALCULATE(SUM([new flow]),FILTER(ALL('table'),[Index]<=EARLIER([Index])))
Best Regards
Maggie
Thanks Maggie.
I tried using the DAX, however, it's giving me Insufficient memory Error. I am running this query on 1 Million rows, in a 8 GB Laptop.
Can you help me to fix this issue.
Hi @singhv2
How about running these query respectively
flag = IF ( [Process Flow] = 0, BLANK (), CALCULATE ( SUM ( 'table'[Process Flow] ), FILTER ( ALLEXCEPT ( 'table', 'table'[POBU] ), [Index] <= EARLIER ( [Index] ) ) ) )
new flow = IF ( flag > [Process Flow], 0, [Process Flow] )
total = CALCULATE(SUM([new flow]),FILTER(ALL('table'),[Index]<=EARLIER([Index])))
Best regards
Maggie
Hi Maggie,
Can you think of some alternate way.... Index <= Earlier(Index) is taking too much time to execute 1 Million rows.
Hi @singhv2
Could you try this formual in a measure
total = CALCULATE(SUM([new flow]),FILTER(ALL('table'),[Index]<=MAX([Index])))
Best Regards
Maggie
Do we have any other way to find out the [New Flow] ? I am stuck with the New Flow Code.
Hi Maggie,
It's still not helping.