All Calc Columns In One Row

[kevinedora]

Hi,

Please need help. I have a matrix table that looks like this

(Measure)Previous month = CALCULATE(SUM('Revenue RAW'[AMORT_AMOUNT]),PREVIOUSMONTH('Calendar Table'[Period]))

(Measure)Current Month = CALCULATE(SUM('Revenue RAW'[AMORT_AMOUNT]),PARALLELPERIOD('Calendar Table'[Period],0,MONTH))

(Measure)Variance = ([Current Month]-[Previous Month]) + 0

It's all good but then I wanted to add one more column saying if there was any "installs" status 3 months ago per account.

(calc column)3months Installs =

VAR _install = maxx(FILTER('OPM RAW','Revenue RAW'[ACCTNO]='OPM RAW'[ACCOUNT_NO] && 'Revenue RAW'[ITEM]='OPM RAW'[ITEM] && 'OPM RAW'[MOVEMENT]="1. INSTALLS" && year('Revenue RAW'[Period])=year('OPM RAW'[PeriodOPM])&&month('Revenue RAW'[Period])=month('OPM RAW'[PeriodOPM])+2),'OPM RAW'[MOVEMENT])

return if(_install="","N","Y")

The problem is if I add the calc column, it duplicates the account no if there's an install status. I wanted all of them to be in just one row. Please advise.

Screenshot of the duplicates if the calc column is = to "Y"

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[kevinedora]

@lbendlin 

Apologies, here are the sample tables:

Revenue Table

PeriodRev	Account_No	Item	Amort Amount
2022-05	200000099	DSZAM	931.51
2022-05	200000188	DSZAA	859.17
2022-05	200000218	DSZAM	931.51
2022-05	200000293	DSS8A	232.73
2022-06	200000099	DSZAM	907.77
2022-06	200000188	DSZAA	831.46
2022-06	200000218	DSZAM	907.77
2022-06	200000293	DSS8A	225.22

OPM Table

PeriodOPM	Account_No	Item	Movement
2022-04	200000099	DSZAM	Installs
2022-04	200000188	DSZAA	Recon
2022-04	200000218	DSZAM	Installs
2022-05	200000293	DSS8A	Installs

Result Table (should be in Matrix)

Again, I wanted all measure or calc columns to be in one row per account. The movement column is finding the movement of the account from the revenue table to the OPM table for the past 3 months(specifically 3 months beyond only) and if there are any installs. Criteria: same account and items. Other columns should be self-explanatory.

I hope this clarifies my concern. Thank you!

[lbendlin · Accepted solution]

see if the attached helps you.