Need help with Formula

[Anonymous]

Hi,

I need help with formula for the below data, I want to sum up the values of only projects in the column Data.

So my result should be like column or measure=5933

Data	value
AP Vouchers Entered	41194
AR Invoices	165812
Cash Mgt Entries	2528
Projects	352
Customer Billing	49159
Expense Reports	32993
Journals Posted	48055
PO Count	1335
Project Activities	85049
Projects	5581

Thanks,

Ravi

[Phil_Seamark · Accepted solution]

Hi @Anonymous

You could add the following Measure

New Measure = CALCULATE(
				SUM('Table'[value]),
					'Table'[Data]="Projects"
					)

 or add a column like this

New Column = IF('Table'[Data]="Projects",'Table'[value],BLANK())

I reckon the measure is the better way to go 🙂

[Anonymous · Accepted solution]

Phil I need one more help, I am trying to create a line and stacked chart with the below data and as shown in the Image1 I have created the stacked part, But I want the values of Login User as the line and the Login user should be excluded from the stacked columns as shown in Image2, how to acheive this?

Data	value	Month
AP Vouchers Entered	41194	Oct
AR Invoices	165812	Oct
Cash Mgt Entries	2528	Oct
Contracts	352	Oct
Customer Billing	49159	Oct
Expense Reports	32993	Oct
Journals Posted	48055	Oct
PO Count	1335	Oct
Project Activities	85049	Oct
Projects	5581	Oct
Login Users	46507	Oct
AP Vouchers Entered	37648	Nov
AR Invoices	144398	Nov
Cash Mgt Entries	2554	Nov
Contracts	382	Nov
Customer Billing	36714	Nov
Expense Reports	27417	Nov
Journals Posted	43999	Nov
PO Count	858	Nov
Project Activities	87161	Nov
Projects	5072	Nov
Login Users	42269	Nov

[Phil_Seamark · Accepted solution]

Hi @Anonymous

You could add the following Measure

New Measure = CALCULATE(
				SUM('Table'[value]),
					'Table'[Data]="Projects"
					)

 or add a column like this

New Column = IF('Table'[Data]="Projects",'Table'[value],BLANK())

I reckon the measure is the better way to go 🙂

[Anonymous]

Thanks Phil, I will go with the measure:)

Regards,

Ravi

[Anonymous · Accepted solution]

Phil I need one more help, I am trying to create a line and stacked chart with the below data and as shown in the Image1 I have created the stacked part, But I want the values of Login User as the line and the Login user should be excluded from the stacked columns as shown in Image2, how to acheive this?

Data	value	Month
AP Vouchers Entered	41194	Oct
AR Invoices	165812	Oct
Cash Mgt Entries	2528	Oct
Contracts	352	Oct
Customer Billing	49159	Oct
Expense Reports	32993	Oct
Journals Posted	48055	Oct
PO Count	1335	Oct
Project Activities	85049	Oct
Projects	5581	Oct
Login Users	46507	Oct
AP Vouchers Entered	37648	Nov
AR Invoices	144398	Nov
Cash Mgt Entries	2554	Nov
Contracts	382	Nov
Customer Billing	36714	Nov
Expense Reports	27417	Nov
Journals Posted	43999	Nov
PO Count	858	Nov
Project Activities	87161	Nov
Projects	5072	Nov
Login Users	42269	Nov

[Phil_Seamark · Accepted solution]

Please try this measure and add it to the line values of your visual.

Logged in Users = CALCULATE(
		SUM('Table'[value]),
		FILTER(
			ALLEXCEPT('Table','Table'[Month]),
			'Table'[Data]="Login Users")
			)

[Anonymous]

Thanks Phil, that work exceptionally well!

After using the formula in the line value, I filtered out the "Login Users" from the Visual level filter and the o/p is as shown