Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have these tables
Table2
Index Overdue situation Ago-20Africa Ago-20Europe
1 Positive overdue >8 days
2 Negative overdue >8 days
3 Positive overdue >90 days
4 Negative overdue >90 days
Table1
Customer number Area >8 days >90 days Date MM_YYYY
1 Africa 10 -20 31.08.2020 Ago-20
2 Europe -3 15 31.08.2020 Ago-20
3 Europe 15 10 31.08.2020 Ago-20
4 Asia -4 30 31.08.2020 Ago-20
5 Africa -30 15 31.08.2020 Ago-20
6 Africa -5 -10 31.08.2020 Ago-20
In Table 2 column "Ago-20Africa" the output should filtering the Area, for example Area = "Africa"
.
Index Overdue situation Ago-20Africa Ago-20Europe
1 Positive overdue >8 days 10
2 Negative overdue >8 days -35
3 Positive overdue >90 days 15
4 Negative overdue >90 days -30
I am using the measure:
Aug-20Africa = var test = CALCULATE(FIRSTNONBLANK ( 'Table1'[Customer number]; 1 );
FILTER(ALL('Table1'); 'Table1'[Area] = "Africa")) return IF([Index] = 1;CALCULATE(SUM('table1'[>8 days]);'table1'[>8 days] > 0)/1000;IF([Index] = 2;CALCULATE(SUM('table1'[>8 days]);'table1'[>8 days] < 0)/1000;IF([Index] = 3; CALCULATE(SUM('Table1'[>90 days]);'Table1'[>90 days] > 0)/1000;if([Index] = 4;CALCULATE(SUM'Table1'[>90 days]);'Table1'[>90 days] < 0)/1000))))))
the result is that I get the same value no matter which "Area" I use as filter.
Thank you in advance!
Solved! Go to Solution.
Hi @Nun
I build Table1 and Table2 like yours to have a test.
Table1, MM_YYYYcolumn is a calculated column.
MM_YYYY = FORMAT(Table1[Date],"MMM")&"_"&FORMAT(Table1[Date],"YY")
Table2:
I build two measures to achieve your goal.
Aug_20Africa =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Aug_20Europe =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Result is as below.
You can download the pbix file from this link: Filter a text to get the open balance.
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.
@Nun So what should the output be for the given data? Can you post an image of your data model? You might be missing a relationship or have a relationship direction that needs changed.
the otuput, filtering the Area in Table1 (Africa in Table2 "Ago-20Africa), Europe in Table2"Ago-20Europe")
Index Overdue situation Ago-20Africa Ago-20Europe
1 Positive overdue >8 days 10 15
2 Negative overdue >8 days -35 -3
3 Positive overdue >90 days 15 25
4 Negative overdue >90 days -30 null
table1
table2
Using the previous formula, I am getting the same results in table2, if I filter Area "EMEIA" or "Americas" or "APAC" in column Ago-20 table2.
Thanks.
Hi @Nun
I build Table1 and Table2 like yours to have a test.
Table1, MM_YYYYcolumn is a calculated column.
MM_YYYY = FORMAT(Table1[Date],"MMM")&"_"&FORMAT(Table1[Date],"YY")
Table2:
I build two measures to achieve your goal.
Aug_20Africa =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Aug_20Europe =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Result is as below.
You can download the pbix file from this link: Filter a text to get the open balance.
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.
thanks a lot for your solution, istead to use SWICTH I used IF, because for some reason I got the same (negative) total for all the var.
So the I used:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |