Create a column with the sum of all rows

[Anonymous]

Good morning, 

I am trying to create 3 calculated columns that show in every row the total sum of specific rows in the same table, at the same time, avoid circular dependencies, and be responsive to slicers that will appear in the dashboard.

I have tried (calculate,all) and (calculate,allexcept) and I don't get the result I'm looking for. Please see the example below:

STATE	ID	UNITS	TYPE	TOTAL UNITS	TOTAL UNITS PER ID	TOTAL UNITS (CAR+VAN) PER ID
FLORIDA	1234	1,000	CAR	5,105	2,700	1,900
FLORIDA	1234	900	VAN	5,105	2,700	1,900
FLORIDA	1234	800	SUV	5,105	2,700	1,900
FLORIDA	4573	760	BIKE	5,105	1,650	890
FLORIDA	4573	890	CAR	5,105	1,650	890
FLORIDA	4578	755	VAN	5,105	755	755

Ultimately I will build a flag that compares the last two columns and show a 1 or 0 depending on the "type", but I really need to show this table (from excel) as a visualization in the dashboard, is it possible?

Thank you for your support.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a column or measure as follows:

1.create a column.

totalPROJECTED = CALCULATE(SUM([PROJECTED]),ALLEXCEPT('Table','Table'[ID]))

flag1 = IF([totalPROJECTED]>[totalunit],1,0)

then put it into table and don't summarize.

2.you also could use measure .

flag2 = 
var _cout=CALCULATE(DISTINCTCOUNT([ID]),FILTER('Table',[totalPROJECTED]>[totalunit]))
return IF(_cout=BLANK(),0,_cout)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Good morning,

Flag 2 worked partially, now I have 3 questions:

1) In some cases where units = projected on an ID I am getting a 1, not a zero, which I think is because at least one type of vehicle in the same ID had units < projected, although the overall sum is equal, do you know what could be happening?

2) How do I make "flag 2" not responsive only to a STATE slicer that will be in the dashboard?

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create it by following:

Column = CALCULATE(SUM([UNITS]),FILTER(ALLEXCEPT('Table','Table'[ID]),[TYPE] in {"VAN","CAR"}))

Column 2 = CALCULATE(SUM([UNITS]),ALL('Table'))

Column 3 = CALCULATE(SUM([UNITS]),ALLEXCEPT('Table','Table'[ID]))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, thank you for answer, it worked perfectly, however, now I have another issue.

I want to create a calculated column as a "Flag" with just 0's and 1's if the units where less than projected. When doing so, I get the addition of all rows where the condition was met, not just using the overall totals, how can I do that?

This is my database:

STATE	ID	TYPE	UNITS	PROJECTED
FLORIDA	1234	CAR	1000	1050
FLORIDA	1234	VAN	900	910
FLORIDA	1234	SUV	800	840
FLORIDA	4573	BIKE	760	800
FLORIDA	4573	CAR	890	900
FLORIDA	4578	VAN	755	700

This is my formula for the flag column:

FLAG = IF (UNITS < PROJECTED, 1, 0)

And this is what I am getting:

STATE	ID	UNITS	PROJECTED	CURRENT FLAG	EXPECTED FLAG
FLORIDA	1234	2700	2800	3	1
FLORIDA	4573	1650	1700	2	1
FLORIDA	4578	755	700	0	0

I understand why the current flag returns those numbers (it is adding the total times the condition was met by "type" and "ID", but I need that flag to be the EXPECTED FLAG column, with just 0's and 1's.

Please note that the TYPE column will be a slicer in the dashboard, not included in the table, so I need the final table to show if the overall ID units (depending on option(s) selected) where less than projections.

Can you help me with that?

I will mark the answer as Solution, but I don't want to create another post, unless that's what's required.

Thank you for your support.