Forum Discussion
Countrows if for each month
Hello,
I have three tables, two with different type of code, each table has a code, an amount column and a date table.
I have a SUMUP table wich is construct like that :
- Month
- Countrows of the first table
- Sum of the amount from the first table
- Countrows of the second table
- Sum of the amount from the second table
I want to add 2 columns to my SUMUP table:
- A countrows if 'table 3'[fruit] = "Apple" distribued for each month
- A countrows if 'table 3'[fruit] = "banana" distribued for each month
I created a pbix file in order to give you some view over my datas.
Best regard,
ScrappyS
Anonymous
I didn't see table 3 in your sample file,so I created one.
please see if this is what you want
Table = var t1=SUMMARIZE(ADDCOLUMNS('Table 1',"_month",month('Table 1'[Month])),[_month],"countrowT1",COUNTROWS('Table 1'),"SUM CA T1",SUM('Table 1'[CA])) var t2=SUMMARIZE(ADDCOLUMNS('Table 2',"_month",month('Table 2'[Month])),[_month],"countrowT2",COUNTROWS('Table 2'),"SUM CA T2",SUM('Table 2'[CA])) VAR t3=SUMMARIZE(ADDCOLUMNS('Table 3',"_month",month('Table 3'[Month])),[_month],"countapple",CALCULATE(COUNTROWS('Table 3'),FILTER('Table 3','Table 3'[fruit]="apple")),"countbanana",CALCULATE(COUNTROWS('Table 3'),FILTER('Table 3','Table 3'[fruit]="banana"))) VAR t4=NATURALINNERJOIN(t1,t2) return NATURALINNERJOIN(NATURALINNERJOIN(t1,t2),t3)Hope this is helpful.
6 Replies
ryan_mayuSuper User
Anonymous
Is this what you want?
column = CALCULATE(COUNTROWS(Feuil1),FILTER(Feuil1,Feuil1[Fruit]=earlier(Feuil1[Fruit])&&Feuil1[Month]=EARLIER(Feuil1[Month])))If not, please share the expected result that you want.
- v-diye-msft
Community Support
Hi Anonymous
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!
