topic Re: Calculating difference per row with initial row filter in DAX Commands and Tips

Calculating difference per row with initial row filter

zself — Thu, 14 Jul 2022 20:51:57 GMT

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.

Re: Calculating difference per row with initial row filter

zself — Thu, 14 Jul 2022 20:55:35 GMT

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)

Re: Calculating difference per row with initial row filter

v-cazheng-msft — Tue, 19 Jul 2022 08:31:56 GMT

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

Re: Calculating difference per row with initial row filter

zself — Thu, 21 Jul 2022 16:33:47 GMT

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

Re: Calculating difference per row with initial row filter

zself — Thu, 21 Jul 2022 22:26:23 GMT

How do I provide sample data?

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

Re: Calculating difference per row with initial row filter

zself — Thu, 21 Jul 2022 22:28:58 GMT

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

Sample files