Forum Discussion

zself's avatar
zself
Regular Visitor
4 years ago

Calculating difference per row with initial row filter

I don't know how to ask this question properly, but here's my quick measure and a screen shot showing what I want.

Investment % difference from 2019 = 
IF(
	NOT ISBLANK(SUM('tbl_ConsolidatedYears'[Investment A])),
			SUM('tbl_ConsolidatedYears'[Investment A]) - CALCULATE(
			SUM('tbl_ConsolidatedYears'[Investment A]),
			'tbl_ConsolidatedYears'[Transaction Year] = "2019"
		),
		CALCULATE(
			SUM('tbl_ConsolidatedYears'[Investment A]),
			'tbl_ConsolidatedYears'[Transaction Year] = "2019"
		)
)

I'm trying to create a visual of the difference of a measure by year, with a set year (filtered) as the base. Here's a screen shot that includes the PowerBI results and the Excel mock-up of what I want. What's inside the red box is what I want, the green box is what I'm getting. 

Thanks in advance for your help! I'm pulling my hair out.

5 Replies

  • zself's avatar
    zself
    Regular Visitor
    4 years ago

    I think I pasted wrong code. Here's my most recent attempt at the measure:

    Investment A difference from 2019 =
    VAR __BASELINE_VALUE =
        CALCULATE(
            SUM('tbl_ConsolidatedYears'[Investment A]),
            'tbl_ConsolidatedYears'[Transaction Year] IN { "2019" }
        )
    VAR __MEASURE_VALUE = SUM('tbl_ConsolidatedYears'[Investment A])
    RETURN
        IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago

    Hi zself ,

     

    May I know whether your issue has been resolved? If you still have problems, could you please provide some sample data of your tables and let me know the relationships between them and how you create your Table visual in Power BI? Thanks in advance!

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

    • zself's avatar
      zself
      Regular Visitor
      4 years ago

      Thank you for the reply. No, my issue remains. I will provide some sample data and such in a separate reply.

    • zself's avatar
      zself
      Regular Visitor
      4 years ago

      How do I provide sample data? 

      Here are screen shots of the PB pages that include a table of the summarized data.

       

    • zself's avatar
      zself
      Regular Visitor
      4 years ago

      Here is a link to a zip file containing the PB Desktop file and the sample data:

      Sample files