How to report sum of two results in separate visuals in a card?

[BarryM]

I have 2 tables in a report. I need to sum the totals from the tables and display them in a card. I have tried to creat a measure for this but the results in the card are way off. The items I need to total are circled in red.

Screen shot:

[bolfri]

BarryM,

can you share a pbix file with dummy data to help you?

[BarryM]

I have provided some data in the replies below, all of what I am using is aggregated into that one table

[bolfri]

It will be easier for everyone if you will share a sample data to help you with that. You can receive an answer much faster than right now. You dont need to give us all the data that you have. You can prepare some dummy data to give us more what you are doing and what are you expecting. 

[BarryM]

Unfortubately due to security restrictions I am not able to connect to a public accessable space where you could access the pbix files.

[bolfri]

That's why I suggest to create a sample data and past them into the PBI file to show us what you have. It can be 4-6 rows only, but applying all things that you are fighting with. 🙂

[BarryM]

Unfortunately I have no means of getting that PBI file to you. I can't attach anything to these posts and I cannot access any file share that will allow me to uplaod any PBIX file. This is why I included a redacted version of the single table in PBI that I am using in the posts below.

[Ashish_Mathur]

Hi,

If the measure in both tables is the same, then just drag that to the card visual.  If not, then i will need to see the file.  Share the link from where i can download your PBI file.

[v-yueyunzh-msft]

Hi, @BarryM 

According to your description, you want to get the sum of total in the table to show it in the card visual. Right?
You need to use the summarize() function to create a virtual table that is the same as your table, and use the addcolumns() function plus your sum measure. Then finally use the sumx() function to find total,like this:

var _t =ADDCOLUMNS(SUMMARIZE(ALLSELECTED(Table),'Table'[Column1 in your table],'Table'[Column2 in your table],....),"measure",[your measure])
return
SUMX(_t,[measure])

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[BarryM]

I have tried to creat the virtual table as you suggested but it through some errors. Here is a sable of the data that I am using.

37663	BI-4531	11601	Financial Costing	02/12/2022 16:36		11306	Created	12311	JIRAUSER12311		D	PS: 10 days x $1,250/day		PS	10			22/23				1	One-Time	1	37663	37663	3738		TDRS	IC46	BROS-416			Threat Detection & Response Services	856215	ESS (Endpoint Security Services)	TDRS		$0.00		$1,250.00
37664	BI-4532	11601	Financial Costing	02/12/2022 16:55		11306	Created	12311	JIRAUSER12311		D			O&M / Non-Salary	1			22/23				1000	One-Time	1	37664	37664	3738		TDRS	IC46	BROS-416			Threat Detection & Response Services	856215	ESS (Endpoint Security Services)	TDRS		$0.00		$4.16
37706	BI-4543	11601	Financial Costing	07/12/2022 16:15		11306	Created	12408	JIRAUSER12408		D	Testing for QTY of 2.5 FW's with Other Costs for FW	Capital	1			22/23				3	One-Time	1	37706	37706	3819		Virtual Private Network	IH43	BROS-425			ISO Protective Security Services	856216	Virtual Private Network	PSS		$0.00		$10,000.00
37707	BI-4544	11601	Financial Costing	07/12/2022 16:22		11306	Created	12408	JIRAUSER12408		D	Testing Decimal Point for Other costs ... using VPN PSS support for certificates	O&M / Non-Salary	1			22/23				100	One-Time	1	37707	37707	3819		Virtual Private Network	IH43	BROS-425			ISO Protective Security Services	856216	Virtual Private Network	PSS		$0.00		$4.50
37708	BI-4545	11601	Financial Costing	07/12/2022 16:26		11306	Created	12408	JIRAUSER12408		D	Testing Flat Rate for TDRS example -- "Standard Cost of $1800.00"	O&M / Salary	2.59	IT-03-SLR - $152,900.00	IT-03-SLR	22/23				1	One-Time	1	37708	37708	3828		E&I	IE53	BROS-426			Security E&I	839514	Design for: Secure Remote Access; Network Security	E&I		$1,800.00	$152,900.00	$0.00	$152,900
37709	BI-4546	11601	Financial Costing	07/12/2022 16:35		11306	Created	12408	JIRAUSER12408		D	TEST for no entry on "Other Costs" field	O&M / Salary	1	IT-02 - $86,213.00	IT-02	22/23				1	One-Time	1	37709	37709	3621		Security E&I	IE53	BROS-403			Security E&I	839411	Director	E&I		$391.88	$86,213.00		$86,213
37710	BI-4547	11601	Financial Costing	07/12/2022 16:41		11306	Created	12408	JIRAUSER12408		D	Testing with making sure "Other Costs" has entry of "$0"	O&M / Salary	1	IT-03-SLR - $152,900.00	IT-03-SLR	22/23				1	One-Time	1	37710	37710	3621		Security E&I	IE53	BROS-403			Security E&I	839411	Director	E&I		$695.00	$152,900.00	$10.00	$152,900
37711	BI-4548	11601	Financial Costing	07/12/2022 16:43		11306	Created	12408	JIRAUSER12408		D	In "Other Cost" field ... just leave as $ with no other value entered	O&M / Salary	2	IT-03-SLR - $152,900.00	IT-03-SLR	22/23				1	One-Time	1	37711	37711	3621		Jeff Conrad	IE53	BROS-403			Security E&I	839411	Director	E&I		$1,390.00	$152,900.00	$23.00	$152,900
37712	BI-4549	11601	Financial Costing	07/12/2022 17:06		11306	Created	12201	JIRAUSER12201					Capital	1	IT-02 - $86,213.00	IT-02	22/23				1		1	37712	37712	3828		E&I	IE53	BROS-426			Security E&I	839514	Design for: Secure Remote Access; Network Security	E&I		$391.88	$86,213.00	$0.00	$86,213
37730	BI-4567	11601	Financial Costing	08/12/2022 19:26	What should be entered here???	11306	Created	11600	JIRAUSER11600		D	FW-UC-2C (5 days) for GOP		O&M / Salary	5	IT-02-SLR - $152,900.00	IT-02-SLR	23/24				1	One-Time	1	37730	37730	3648		TDRS	IC91	BROS-406			Threat Detection & Response Services	855211	SIEM (Security Info & Event Mgt)	TDRS		$3,475.00	$152,900.00	$0.00	$152,900
37731	BI-4568	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D			Capital	12			23/24				2	One-Time	1	37731	37731	3657		Threat Detection & Response Services	IE44	BROS-407			Threat Detection & Response Services	855313	ETI SOC Admin Investigations	TDRS		$0.00		$5,000.00
37732	BI-4569	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D	Capital Cost 2 x $ 50,000.00 Fortinet equipment	Capital	1			22/23				2	One-Time	1	37732	37732	3774		ISO Protective Security Services	IN97	BROS-420			ISO Protective Security Services	856314	Firewall - Zone Security	PSS		$0.00		$50,000.00
37733	BI-4570	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D	Salary IT-04: 10 days		O&M / Salary	10	IT-04 - $118,499.00	IT-04	22/23				1	One-Time	1	37733	37733	3729		Threat Detection & Response Services	IC46	BROS-415			Threat Detection & Response Services	856213	ESS (Endpoint Security Services)	TDRS		$5,386.32	$118,499.00	$1.00	$118,499
37734	BI-4571	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D			O&M / Salary	5	IT-03-SLR - $152,900.00	IT-03-SLR	22/23				1	On-going	4	37734	37734	3819		Virtual Private Network	IH43	BROS-425			ISO Protective Security Services	856216	Virtual Private Network	PSS		$868.75	$152,900.00	$1.00	$38,225
37735	BI-4572	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D	PS: 10 days x $1,250/day		PS	10			22/23				1	One-Time	1	37735	37735	3738		TDRS	IC46	BROS-416			Threat Detection & Response Services	856215	ESS (Endpoint Security Services)	TDRS		$0.00		$1,250.00
37736	BI-4573	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D			O&M / Non-Salary	1			22/23				1000	One-Time	1	37736	37736	3738		TDRS	IC46	BROS-416			Threat Detection & Response Services	856215	ESS (Endpoint Security Services)	TDRS		$0.00		$4.16
37737	BI-4574	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D	Capital cost 5 X 10,000/unit		Capital	1			22/23				5	One-Time	1	37737	37737	3738		TDRS	IC46	BROS-416			Threat Detection & Response Services	856215	ESS (Endpoint Security Services)	TDRS		$0.00		$10,000.00
37738	BI-4575	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D	SLR Salary		O&M / Salary	5	IT-05-SLR - $152,900.00	IT-05-SLR	23/24				1	One-Time	1	37738	37738	3819		Virtual Private Network	IH43	BROS-425			ISO Protective Security Services	856216	Virtual Private Network	PSS		$3,475.00	$152,900.00	$0.00	$152,900
37739	BI-4576	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D			O&M / Salary	10	IT-05-SLR - $152,900.00	IT-05-SLR	23/24				1	One-Time	1	37739	37739	3648		TDRS	IC91	BROS-406			Threat Detection & Response Services	855211	SIEM (Security Info & Event Mgt)	TDRS		$6,950.00	$152,900.00	$0.00	$152,900
37740	BI-4577	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D			Capital				23/24				10	One-Time	1	37740	37740	3648		TDRS	IC91	BROS-406			Threat Detection & Response Services	855211	SIEM (Security Info & Event Mgt)	TDRS		$0.00		$10,000.00
37741	BI-4578	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600					O&M / Salary	10	IT-01-SLR - $152,900.00	IT-01-SLR	22/23				1		1	37741	37741	3621		Security E&I	IE53	BROS-403			Security E&I	839411	Director	E&I		$6,950.00	$152,900.00	$0.00	$152,900
37742	BI-4579	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D	PS: 10 days x $ 1,150.0/day		PS	10	IT-02 - $86,213.00	IT-02	24/25				1	On-going	1	37742	37742	3720		Threat Detection & Response Services	IO97	BROS-414			Threat Detection & Response Services	856812	Cyber Incident Response Team	TDRS		$3,918.77	$86,213.00	$1,150.00	$86,213
37743	BI-4580	11601	Financial Costing	08/12/2022 19:26		11306	Created	11600	JIRAUSER11600		D			O&M / Non-Salary	220	IT-04 - $118,499.00	IT-04	23/24				1	On-going	1	37743	37743	3792		ISO Protective Security Services	IN97	BROS-422			ISO Protective Security Services	856316	Firewall - Zone Security	PSS		$118,499.00	$118,499.00	$0.00	$118,499
37762	BI-4592	11601	Financial Costing	12/12/2022 19:32		11306	Created	12311	JIRAUSER12311		D			Capital	11	IT-02-SLR - $152,900.00	IT-02-SLR	23/24				10	One-Time	1	37762	37762	3621		Security E&I	IE53	BROS-403			Security E&I	839411	Director	E&I		$7,645.00	$152,900.00	$1,099.00	$152,900
37763	BI-4593	11601	Financial Costing	12/12/2022 19:35		11306	Created	12311	JIRAUSER12311		D			O&M / Salary	10	IT-01-SLR - $152,900.00	IT-01-SLR	23/24					One-Time	1	37763	37763	3865		ssc.directoryservices-servicesdannuaire.spc@canada.ca	IE53	BROS-430			Security E&I	839414	esign for: Secure Remote Acces121255s; Network Security	E&I - Services		$6,950.00	$152,900.00		$152,900

[BarryM]

Hi,

Thanks for replying...I do have one question regarding the virtual table creation you suggested. When you indicated "create a virtual table that is the same as your table", is it the virtual table to be the same as the table in the visual or the table that the visual is drawing the data from?

[v-yueyunzh-msft]

Hi , @BarryM 

I mean defining virtual  table using var, like I said above.

var _t =ADDCOLUMNS(SUMMARIZE(ALLSELECTED(Table),'Table'[Column1 in your table],'Table'[Column2 in your table],....),"measure",[your measure])

Use this function to create a table that looks like your table viusal, and then use sumx to find total.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[djurecicK2]

Hi @BarryM ,

 It seems like you have the right approach- creating a measure (or 2 measures and adding them together).

What measures have you tried?

[BarryM]

re is the dax code for what I have tried 

Salery Cost total for BR_12713 =

CALCULATE(

    SUM('BR Sub Issues and attributes'[Salary Cost per FY]),

    ALLSELECTED('BR Sub Issues and attributes'[Id])