Forum Discussion
How to sum up data based on condition
Hi
Need help on below query
Created below measure to get data.
Below is the sample data
| propcode | Name[Lease-SFV] | chargepostmonth | chargetotalamt | SecondYearRent | Occupied ratio |
| atl00021 | atl00021-AAA | 10/1/2021 | 235 | 1000 | 1 |
| Atl00021 | atl00021-BBB | 9/1/2021 | 10 | 1000 | 1 |
| atl00045 | atl00045-CCC | 10/1/2021 | 250 | 2000 | 1 |
| atl00076 | atl00076-DDD | 10/1/2021 | 195 | 3000 | 1 |
| atl00127 | atl00127-EEE | 10/1/2021 | 230 | 2000 | 1 |
| atl00127 | atl00127-XXX | 9/1/2021 | 255 | 2000 | 1 |
If we choose slicer date as October , I need to sum up chargetotalamt for october month data to SUM(LEASE_SFV[SecondYearRent]) * [OccupiedRatio_Current] ) for that perticular property.
CALCULATE( --Month IF( ( DATEDIFF( MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) > 12 && VALUES(ReportPeriod[Type]) = "Month" ), ( SUM(LEASE_SFV[SecondYearRent]) * [OccupiedRatio_Current] ) + IF( ( DATEDIFF( MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) > 12 && VALUES(ReportPeriod[Type]) = "Month" ) && FORMAT(MAX(PREMINUMS_YV[chargepostmonth]),"YYYYMM") = FORMAT(_dateM,"YYYYMM") , SUM(PREMINUMS_YV[chargetotalamt]),0) , IF( ( DATEDIFF( MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) <= 12 && VALUES(ReportPeriod[Type]) = "Month" ), ( SUM(LEASE_SFV[Rent_on_Lease_Agreement__c]) * [OccupiedRatio_Current] ) + IF( ( DATEDIFF( MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) <= 12 && VALUES(ReportPeriod[Type]) = "Month" ) && FORMAT(MAX(PREMINUMS_YV[chargepostmonth]),"YYYYMM") = FORMAT(_dateM,"YYYYMM") , SUM(PREMINUMS_YV[chargetotalamt]),0) |
But I am getting below result.
| propcode | Name[Lease-SFV] | chargepostmonth | chargetotalamt | SecondYearRent | Occupied ratio | Expected Result(chargetotalamt + (SecondYearRent * Occupied ratio) |
| atl00021 | atl00021-AAA | 10/1/2021 | 235 | 1000 | 1 | 1235 |
| atl00045 | atl00045-CCC | 10/1/2021 | 250 | 2000 | 1 | 2250 |
| atl00076 | atl00076-DDD | 10/1/2021 | 195 | 3000 | 1 | 3195 |
| atl00127 | atl00127-EEE | 10/1/2021 | 230 | 2000 | 1 | 2230 |
1 Reply
Hi TriveniN ,
As checked your formula, it seems involve multiple tables. Is there any relationship created among them? Could you please provide some source data in these tables 'LEASE_SFV', 'PREMINUMS_YV' and 'ReportPeriod' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is _dateM a variable? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
