Forum Discussion
Resolver IRemove 0's in a row sum
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,
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 _SumbyCompanyYou 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- Ivancito111
.
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 _SumbyCompanyFilter = 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 _SumbyCompanyCreate 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 ZhouIf 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 __SumbyCompanyM_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 __SumbyCompanyFor security reasons I cannot send you more information about my company.
Regards,
