Showing Totals Without negative amounts per customer

[PowerBIAnalist]

Hi, 

I'm trying to calculated how much rent people still have to pay and total rent that still has to be paid by all customers. This should not include any total rents per customer that have to be paid which are negative. 

As example, I have the following start table: 

Customer	Date	Late rent	Prepaid rent
C01	31-5-2021	500	0
C01	31-5-2021	0	50
C02	31-5-2021	0	60
C03	31-5-2021	600	0
C03	31-5-2021	0	400
C01	30-6-2021	300	0
C01	30-6-2021	0	350
C02	30-6-2021	400	0
C03	30-6-2021	200	0
C03	30-6-2021	0	50

If I calculate the rent that has to be paid per customer per month (late rent - prepaid rent) I should get a table looking like this:

Customer	Date	Rent end of month
C01	31-5-2021	450
C02	31-5-2021	-60
C03	31-5-2021	200
C01	30-6-2021	-50
C02	30-6-2021	400
C03	30-6-2021	150
Total		1090

Now I want to be able to add a slicer from the date table where I'm able to filter per month, so let's say june. I would expect something like this:

Customer	Date	Rent end of month
C01	30-6-2021	-50
C02	30-6-2021	400
C03	30-6-2021	150
Total		500

I also don't want to show the negative rents at the end of the month, so the end result would be something like this:

Customer	Date	Rent end of month
C02	30-6-2021	400
C03	30-6-2021	150
Total		550

Plus I want to show only the total of alll rents that has to be paid, so 550.

I have been able to create the table the way I want and show the correct total in the table. But as soon as a delete the customernumbers to show only the total rent that has to be paid it includes the negative rents again. 

Things I have tried: 

- Rent at the end of the month = IF(AND(HASONEVALUE(Rents(customer), (SUM(Rents(late rent)-Sum(Rents(Prepaid rent)), SUMX, rents, (SUM(Rents(late rent)-Sum(Rents(Prepaid rent)))

- As soon as i create a measure for if the rent is positive or not, it only looks at each row, but it should look at the totals of each customer per date. 

- Creating a table with customer per date and the rent that has to be paid, but then I'm not able to filter the date in this table with the date from the datetable which I use

Points: 

- I should be able to filter the results per date based on the date table

- I should be able to see the rent that has to be paid per customer per month

- I should be able to filter out all negative rents

- I should be able to see the total rent that has to be paid per month in a seperate visual without the negative rents that have to be paid

Really hope someone can help! 

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ,

Add new column on table2 what you want:

pay1 =
MAXX (
    FILTER (
        'Customer payment arrangement',
        'Customer payment arrangement'[Date] = Table2[Date]
            && 'Customer payment arrangement'[Customer] = Table2[Customer]
    ),
    'Customer payment arrangement'[Payment arrangement]
)

Best Regards

Lucien

[PowerBIAnalist]

Thank you! That works, but I have a lot of information that I would have to put in Table2 and I have also other information that is not directly linked to Table2. I sadly can't share the orginal file with you because it's completely full of personal data. 

Thanks a lot for trying to help me! 

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[PowerBIAnalist]

Hi! Thank you for your reminder. My problem hasn't been solved yet. I tried your last suggestion but I still had problems. 

If I use the date from Date2 to filter, the Table is filtered correctly, however the other information about the customer is not filtered. So i get the amounts and customers twice, and see Yes and no for the payment arrangements because that table isn't filtered with the date from Date2. Do you have a suggestion for this? 

Kind regards, 

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ，

Could you pls  provide me with your original pbix file after adjusting the confidential data?My pbix sample seems to keep differing from yours.

Best Regards

Lucien

[PowerBIAnalist]

Hi, 

I've adapted my Test power bi file with your Date2 table and it still doesn't work. If you look at month 5 it shows yes and no for Howard, because it doesn't create a link with the date. It should only show no. In Month 6 you don't have this problem, because C02 and C03 have yes or no for both months. 

https://drive.google.com/file/d/1JnNG8geFxjfn48N0ztOLFJFslAAzrXHJ/view?usp=sharing 

I hope you can download it here! 

Thank you, 

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ,

Payments contains is  based on Date,so we  need to keep the relationship between Payments contains  and  Date，right？

And if you could create a new table name Date2='Date', then create relationship between Date2 and Table2 ,just like below,then use the column in Date2 in slicer?

Best Regards

Lucien

[PowerBIAnalist]

Hi @v-luwang-msft , 

I've just tried this. If I use the date from Date2 to filter, the Table is filtered correctly, however the other information about the customer is not filtered. So i get the amounts and customers twice, and see Yes and no for the payment arrangements because that table isn't filtered with the date from Date2. 

Kind regards, 

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ,

I have a test ,

first ,change relationship to the below:

Then change the data type:

Final you will see the below:

Wish it is helpful for you!

Best Regards

Lucien

[PowerBIAnalist]

Hi @v-luwang-msft, 

Thank you for your time! You have been a great help and I feel like I'm getting there! However, this sollution works for the example PBIX I have sent you. But in the original file my table with the rent payments contains a date key and I have used the related function to import the date to the table and then used that one for the Table which i created using your formula. 

If I now disconnect the relationship between the date table and rent payments table, I no longer have a date in the table because the tables are no longer related. Which leads to failures and not being able to filter using the date or other filters. 

I hope you understand what I mean. 

Again, thank you so much for your help! I felt like giving up, but I have hope again!

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ，

Could you pls share your pbix file ?

Just reading the text description is not very clear.Remember to remove confident data.

Best Regards

Lucien

[PowerBIAnalist]

Hi @v-luwang-msft, 

I have updatet the file which you send me to look like the PBIX I have. As you can see I have now created a date filter and some other filters. I have connected the tables but the filters don't work on the table which I created using your formula. In reality it's a lot more data that has to be filtered, so adding them all to the created table with the rent at the end of the month will probably result in Power BI crashing on my computer. I hope this gives you some clarity, otherwise please let me know!

Showing totals file updated (I hope you can download this file, I am not able to share files in the communicty)

Thank you!

Liza

[v-luwang-msft]

Hi @PowerBIAnalist ，

You could try the following steps:

base  data:

Step 1,use the following measure to create a new table:

Table2 =
SUMMARIZE (
    'Table',
    'Table'[Customer],
    'Table'[Date],
    "Rent end of month",
        IF (
            SUM ( 'Table'[Late rent] ) - SUM ( 'Table'[Prepaid rent] ) >= 0,
            SUM ( 'Table'[Late rent] ) - SUM ( 'Table'[Prepaid rent] ),
            BLANK ()
        )
)

Then get below :

Step 2, create visulization,it what you want:

You could download my pbix file if you need.

Wish it is helpful for you!

Best Regards

Lucien

[PowerBIAnalist]

Hi @v-luwang-msft , 

Thank you so much for your response! I succeeded in making the table and it shows the correct amount of rent at the end of the month and I am able to show the total rent that has to be paid. However, I have a date table in my dashboard which I use for filtering. I have to be able to filter this visual/table with the rents at the end of the month with the date table filter. This is now not possible. Do you have a sollution for this? 

I have now connected the table which I created to the source table with the customer number. The source table is connected to the date table. However, then I can't filter the visial with the date from the date table. But I can add more customer data to the visual. 

If I connect the table which I created to the date table with the date, I am able to filter the visual with the date table. However, I can't add any more customer data which I do have to add. 

Hope you can help! 

Liza

[amitchandak]

@PowerBIAnalist , Try a measure like

Sumx(values(Rents[customer]), if(SUM(Rents[late rent])-Sum(Rents[Prepaid rent]) >0 , SUM(Rents[late rent])-Sum(Rents[Prepaid rent]) , blank()))