Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lucy64
Helper III
Helper III

Running Total

I am trying to calculate a running total for a production line based on the Machine name, there is no date value.  I am using a Direct Query to SQL database.  I want to add an exta column for running total of FNV, is this possible?

 

eg1.PNG

1 ACCEPTED SOLUTION
jahida
Impactful Individual
Impactful Individual

Eyy, I think I got it. Try this:

 

RunningTotal2 = MAXX(Table1, SUMX(
FILTER(
	SUMMARIZE(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName], "NetVal", SUM(Table1[Fabrication Net Value])),
	[NetVal] >=
		SUMX(FILTER(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName]= EARLIER(Table1[MachineName], 2)), Table1[Fabrication Net Value]))
, [NetVal]))

 

Hopefully you can see how that would scale out to other columns if you had multiple filters.

View solution in original post

30 REPLIES 30
Lucy64
Helper III
Helper III

Thanks for your help, unfortunately I could not get any of these solutions to work.  Please remember I am using Direct Query so calculated columns is not an option.  I have however succeded in creating a measure to calculate the ranking for each machine so maybe I could use that in the calculation.  I created this one with dates and although it works does not serve for my needs:

 

CumulativeFabricationNetValueDate = CALCULATE ( SUM (vwScalesDataFabricationNetValue[Fabrication Net Value]), FILTER(ALL(vwScalesDataFabricationNetValue[WeightGeneralDate]), vwScalesDataFabricationNetValue[WeightGeneralDate] <= MAX(vwScalesDataFabricationNetValue[WeightGeneralDate])))

 

I tried doing something similar with the ranking measure instead of date but as yet no luck.

 

 

jahida
Impactful Individual
Impactful Individual

Did you try the measure I included earlier, and if so what was the result?

Here is the measure I used and the results:

 

RunningTotal = MAXX(vwScalesDataFabricationNetValue, CALCULATE(SUM(vwScalesDataFabricationNetValue[Fabrication Net Value]), vwScalesDataFabricationNetValue[Fabrication Net Value] >= EARLIER(vwScalesDataFabricationNetValue[Fabrication Net Value]), ALL(vwScalesDataFabricationNetValue)))

 

RankingMachineNameFabrication Net ValueRunningTotal
1Quarrata2100374934
2Rollpacks1675287249
3Tightwinder1239078010
4T8 ( 2 )1021787114
5T8 ( 4 )582188776
6Cut off saw580189000
7OEM VCC's513388822
8T8 ( 3 )250985088
9T8 (1 )215576690
10Tarp saw190688949
11Mattress143579222
12OEM CNC112689020
13T8 ( 5 )88988625
14Fillmatic72435792
15Quality Samples49785254
16C52 Pillow Machine30988977
17Bun Roller Machine22962330
18Chinese Carousel12478134
jahida
Impactful Individual
Impactful Individual

Is each row of the table showing one row in the source data, or a number of rows in the source data?

 

EDIT: If you have the ranking created already, you can probably just replace the Fabrication Net Value with Ranking everywhere but in the SUM, and it should work.

It is the total of several rows per machine.

jahida
Impactful Individual
Impactful Individual

If you have the ranking created already, you can probably just replace the Fabrication Net Value with Ranking everywhere but in the SUM, and it should work.

 

I could write a new measure but this seems like the easiest way.

 

MAXX(vwScalesDataFabricationNetValue, CALCULATE(SUM(vwScalesDataFabricationNetValue[Fabr​ication Net Value]), vwScalesDataFabricationNetValue[Ranking] >= EARLIER(vwScalesDataFabricationNetValue[Ranking]), ALL(vwScalesDataFabricationNetValue)))

Tried it but got an error :

 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

The ranking is a measure not a calculated column, does that make a difference?  I have underlined the section highlighted for error.

 

Running Total = MAXX(vwScalesDataFabricationNetValue, CALCULATE(SUM(vwScalesDataFabricationNetValue[Fabrication Net Value]), vwScalesDataFabricationNetValue[Ranking] >= EARLIER(vwScalesDataFabricationNetValue[Ranking]), ALL(vwScalesDataFabricationNetValue)))

 

jahida
Impactful Individual
Impactful Individual

Ok, yeah the fact that it's a measure does make a difference, but I should have guessed that, my bad. Anyway, try this:

 

RunningTotal2 = MAXX(Table1, SUMX(
FILTER(
	SUMMARIZE(ALL(Table1), Table1[MachineName], "NetVal", SUM(Table1[Fabrication Net Value])),
	[NetVal] >=
		SUMX(FILTER(ALL(Table1), Table1[MachineName]= EARLIER(Table1[MachineName], 2)), Table1[Fabrication Net Value]))
, [NetVal]))

 

Works on my local, although my testing data might not be perfect (obviously it wasn't the first time).

Thanks, well that worked for all the data, now what if I want to filter.  The Machines are alocated to Buildings so when I filter on building it does not recalc, is this where I need to use ALLEXCEPT instead of ALL? 

 

By the way I am very greatful for your help.

Actually I noticed something very interesting, look at this:

 

Capture.PNG

 

Although the running total does not recalculate when I show as a percentage it does recalc on filter,  the % is what I am after so I believe this solution will work for me.

jahida
Impactful Individual
Impactful Individual

Hm, ok. I'm trying to work through a solution that allows filtering, since ALLEXCEPT only plays nice when it's in a CALCULATE statement, but if that works for you then great. Still going to try to finish if only for the intellectual value.

jahida
Impactful Individual
Impactful Individual

Eyy, I think I got it. Try this:

 

RunningTotal2 = MAXX(Table1, SUMX(
FILTER(
	SUMMARIZE(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName], "NetVal", SUM(Table1[Fabrication Net Value])),
	[NetVal] >=
		SUMX(FILTER(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName]= EARLIER(Table1[MachineName], 2)), Table1[Fabrication Net Value]))
, [NetVal]))

 

Hopefully you can see how that would scale out to other columns if you had multiple filters.

Anonymous
Not applicable

thanks for this! @jahida 

There is a MAXX function there. POWER BI tells me that kind of functions cant be used in calculated columns in DIRECT QUERY mode.

What am i missing?

jahida
Impactful Individual
Impactful Individual

It should be a Measure, not a Column.

jahida
Impactful Individual
Impactful Individual

One more note, the results you listed above as the solution are not correct. They're a weird % of the largest number in the grand total column, which isn't what you want I think. Make sure you switch to the more recent formula I provided.

 

Sorry for the spam.

I cannot get the last version to work,  this is what I wrote:

 

RunningTotal2 = MAXX(vwScalesDataFabricationNetValue, SUMX(

FILTER(

   SUMMARIZE(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED(vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName], "NetVal", SUM(vwScalesDataFabricationNetValue[Fabrication Net Value])),

   [NetVal] >=

       SUMX(FILTER(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED((vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName]= EARLIER(vwScalesDataFabricationNetValue[MachineName], 2)), vwScalesDataFabricationNetValue[Fabrication Net Value])), [NetVal])))

 

The error message I got was "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2." and I have put in red the sections that were highlighted.

 

jahida
Impactful Individual
Impactful Individual


@Lucy64 wrote:

@I cannot get the last version to work,  this is what I wrote:

 

RunningTotal2 = MAXX(vwScalesDataFabricationNetValue, SUMX(

FILTER(

   SUMMARIZE(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED(vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName], "NetVal", SUM(vwScalesDataFabricationNetValue[Fabrication Net Value])),

   [NetVal] >=

       SUMX(FILTER(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED((vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName]= EARLIER(vwScalesDataFabricationNetValue[MachineName], 2)), vwScalesDataFabricationNetValue[Fabrication Net Value])), [NetVal])))

 

The error message I got was "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2." and I have put in red the sections that were highlighted.

 


Remove the two brackets in red. They weren't in the initial formula

Thanks, as you can see I am new to DAX, I really need a crash course!  This one did the trick.   Great job!

Capture.PNG

So now we are adding a hierarchy for Plant, Building, Production Line, Machine, how do I incorporate this into the query so that the figures recalc as I drill down and drill up.

 

I though I had posted this yesterday but did not see it today so sorry if I am repeating.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.