topic how to get calculated column with sum by region in one table by another in DAX Commands and Tips

how to get calculated column with sum by region in one table by another

analyticsforall — Tue, 09 Aug 2022 14:41:21 GMT

Hello!

I'm really stucked on this subject, hope someone could help (:

Here is a pbix file https://drive.google.com/file/d/1MUH1ZiFLKCyyNO5fNRDOunY2GAA_vHjY/view?usp=sharing

The question is:

I have 2 tables: PnL and PnL by regions.

Both of them have 2 connections: with calendar and PnL mapping.

In table PnL by regions a have a calculated column called "CIS Rev1", where I need to see all sum from PnL table by same month and year for this region.

For example, for jan 2021 there should ne value per each row = 542 262, but I have only value for 1 day of jan 2021.

Re: how to get calculated column with sum by region in one table by another

Jihwan_Kim — Tue, 09 Aug 2022 18:29:18 GMT

Hi,

Please check the attached file, if it suits your requirement.

I fixed the DAX formula of the calculated column.

CIS Rev1 = VAR CurYear = 'Pnl by Regions'[Year] VAR CurMonth = 'Pnl by Regions'[Month] RETURN SUMX ( FILTER ( PnL, PnL[PNL name] = "CIS" && PnL[Year] = CurYear && PnL[Month] = CurMonth ), PnL[Amount USD] )

Re: how to get calculated column with sum by region in one table by another

analyticsforall — Tue, 09 Aug 2022 14:19:14 GMT

Jihwan_Kim this is exactly what I needed!) Thank you! 🤗