Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need help with Formula

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 Vouc...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
    Anonymous
    9 years ago

     

    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?

     

    DatavalueMonth
    AP Vouchers Entered41194Oct
    AR Invoices165812Oct
    Cash Mgt Entries2528Oct
    Contracts352Oct
    Customer Billing49159Oct
    Expense Reports32993Oct
    Journals Posted48055Oct
    PO Count1335Oct
    Project Activities85049Oct
    Projects5581Oct
    Login Users46507Oct
    AP Vouchers Entered37648Nov
    AR Invoices144398Nov
    Cash Mgt Entries2554Nov
    Contracts382Nov
    Customer Billing36714Nov
    Expense Reports27417Nov
    Journals Posted43999Nov
    PO Count858Nov
    Project Activities87161Nov
    Projects5072Nov
    Login Users42269Nov
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

Thanks Phil, I will go with the measure:)

 

Regards,

Ravi

Anonymous's avatar
Anonymous
Not applicable
9 years ago

 

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?

 

DatavalueMonth
AP Vouchers Entered41194Oct
AR Invoices165812Oct
Cash Mgt Entries2528Oct
Contracts352Oct
Customer Billing49159Oct
Expense Reports32993Oct
Journals Posted48055Oct
PO Count1335Oct
Project Activities85049Oct
Projects5581Oct
Login Users46507Oct
AP Vouchers Entered37648Nov
AR Invoices144398Nov
Cash Mgt Entries2554Nov
Contracts382Nov
Customer Billing36714Nov
Expense Reports27417Nov
Journals Posted43999Nov
PO Count858Nov
Project Activities87161Nov
Projects5072Nov
Login Users42269Nov
  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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