Remove 0's in a row sum

[Ivancito111]

Good afternoon dear, I have a doubt a little complicated to explain, I will try to do the best I can and any kind of information is of interest to me.

In my company they have credit applications, what happens is that they have an intake, interest and payments, which can be all in one installment or 300, the value is very changeable.

Within the data I have the company, the credit id, the bank, the type (take, interest, payment), the period, credit, debit and the balance (credit-debit).

N°companyID_CreditBankTypeperiodCreditDebitBalance

1	dog	CLP - 1	Estado	take	202106	1000		1000
2	cat	CLP - 1	Chile	take	202106	1000		1000
3	rabbit	UF -1	BBA	take	202106	1000		1000
4	fly	UF -1	Estado	take	202106	1000		1000
5	dog	CLP - 1	Estado	interest	202107	100		100
6	cat	CLP - 1	Chile	interest	202107	100		100
7	rabbit	UF -1	BBA	interest	202107	100		100
8	fly	UF -1	Estado	interest	202107	100		100
9	dog	CLP - 1	Estado	payment	202108		-300	-300
10	cat	CLP - 1	Chile	payment	202108		-300	-300
11	rabbit	UF -1	BBA	payment	202108		-300	-300
12	fly	UF -1	Estado	payment	202108		-300	-300
13	dog	CLP - 1	Estado	take	202109	1000		1000
14	cat	CLP - 1	Chile	interest	202109	300		300
15	rabbit	UF -1	BBA	payment	202109		-400	-400
16	fly	UF -1	Estado	payment	202109		-400	-400
17	dog	CLP - 1	Estado	interest	202110	300		300
18	cat	CLP - 1	Chile	interest	202110	300		300
19	rabbit	UF -1	BBA	payment	202110		-400	-400
20	fly	UF -1	Estado	payment	202110		-400	-400

What they want to see is a matrix that shows all the credit data, how much has been paid, how much interest has been generated and how much the loan was taken out.

The problem is that doing it this way shows me the credits that are already paid (sum of balance = 0), but if I add a filter of balance != 0 it doesn't work because obviously there is no row where the balance is 0 in the data.

After some research I found an idea to create a summary table where the sum is done and add this sum in the table filter and make it non-zero, which would eliminate the problem.

I add the sum filter

But doing this does not allow me to filter by credit period, as they need to see a date before the date they chose, if they choose a 2108 period, they want to see the 2108, 2107 and 2106 period, making doing it this way does not work for me.

If anyone knows of a way to do the same I remain attentive to comments.
Thank you very much for reading my problem.
Regards,

[Ivancito111]

.

[Anonymous]

Hi @Ivancito111 

I suggest you to create an unrelated Period table to create the slicer. Then create a measure to calcualte Balance dynamicly filtered by this slicer and create another measure to filter this visual dynamic by this slicer.

Period = VALUES('Table'[Period])

Measures:

M_Balance =
VAR _SelectPeriod =
    IF (
        ISFILTERED ( Period[Period] ),
        SELECTEDVALUE ( Period[Period] ),
        MAX ( Period[Period] )
    )
VAR _SumbyCompany =
    CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER ( 'Table', 'Table'[Period] <= _SelectPeriod )
    )
RETURN
    _SumbyCompany

Filter =
VAR _SelectPeriod =
    IF (
        ISFILTERED ( Period[Period] ),
        SELECTEDVALUE ( Period[Period] ),
        MAX ( Period[Period] )
    )
VAR _SumbyCompany =
    CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Company] = MAX ( 'Table'[Company] )
                && 'Table'[Period] <= _SelectPeriod
        )
    )
RETURN
    _SumbyCompany

Create a Matrix, add [Filter] measure into filter field and set it to show items when value =1.

By Defualt:

Select 202108, all company will show values before 202108.(2108,2107,2106)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ivancito111]

Hello, thank you very much for your answer, the filter works correctly but it does not remove the 0 values from the matrix.

I copied and pasted what you did, and it does not work, I still get the $ 0.

I don't understand why this is wrong, since I replicated the same thing you did in my test data (the animal company) and it works correctly but in the real data, they still appear.

Filter = 
var __SelectPeriod =
    IF(
        ISFILTERED( Slicer[Periodo]),
        SELECTEDVALUE(Slicer[Periodo]),
        MAX(Slicer[Periodo])
    )

var __SumbyCompany =
    CALCULATE(
        SUM(Creditos[Saldo]),
        FILTER(
            ALL(Creditos),
            Creditos[Nombre] = MAX( Creditos[Nombre])
                && Creditos[Periodo] <= __SelectPeriod
        )
    )
return 
    __SumbyCompany

M_Saldo = 
var __SelectPeriod = 
    IF(
        ISFILTERED(Slicer[Periodo]),
        SELECTEDVALUE(Slicer[Periodo]),
        MAX(Slicer[Periodo])
    )
var __SumbyCompany = 
    CALCULATE(
        SUM(Creditos[Saldo]),
        FILTER( Creditos, Creditos[Periodo] <= __SelectPeriod)
    )

return 
    __SumbyCompany

For security reasons I cannot send you more information about my company.

Regards,

[Anonymous]

Hi @Ivancito111 

Please check whether [Period] column you use in slicer has relationship with your data table. You need to create an unrelated table and then create measures based on this [Period]. Relationship will impact your calculate and return incorrect result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ivancito111]

After re-analyzing my data, I found the problem, added it to the data test, and now I realize that what you did doesn't work either, it keeps showing you zeros.

I added this row to the data in the file you sent me, obviously since a company can have more than one credit.

21

rabbit

UF -2

Estado

take

202110

1000

1000

And this is what happens with the matrix

If you notice the credit UF - 1, from the Rabbit company is not eliminated from the matrix and shows zero, my idea is that this zero does not exist, since it was paid.

Regards,

[Anonymous · Accepted solution]

Hi @Ivancito111 

I think you can add some filter in your Filter measure to achieve your goal.

New code:

M_Filter = 
VAR _SelectPeriod =
    IF (
        ISFILTERED ( Period[Period] ),
        SELECTEDVALUE ( Period[Period] ),
        MAX ( Period[Period] )
    )
VAR _SumbyCompany =
    CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Company] = MAX ( 'Table'[Company] )
                && 'Table'[ID_Credit] = MAX ( 'Table'[ID_Credit] )
                && 'Table'[Bank] = MAX ( 'Table'[Bank] )
                && 'Table'[Period] <= _SelectPeriod
        )
    )
RETURN
    _SumbyCompany

You see in my new Table I add UF-2 in rabbit as well, UF-1 will return 0 and UF-2 doesn't. Right one is the visual with Filter measure.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ivancito111]

I don't understand how you did it, but thank you very much, it's just what I needed ❤️

I love You