Forum Discussion
Helper VPower BI counting issue
Hi,
I have two tables and i want a monthly Pivot table to be combined from these two tables with a measure.
Table one:
| Date | Values |
| 06/06/2020 | Text |
| 26/06/2020 | Text |
| 06/06/2020 | Text |
| 05/05/2020 | Text |
| 01/05/2020 | Text |
| 26/05/2020 | Text |
Table two:
| Date | Values |
| 01/01/2020 | 10 |
| 01/02/2020 | 20 |
| 01/03/2020 | 30 |
| 01/04/2020 | 25 |
| 01/05/2020 | 40 |
| 01/06/2020 | 5 |
Pivot: I want to count all rows from table one that are NOT June (3) and add them to the sum of table two that are in June. How do I do this measure? The outcome should be:
Pivot Table:
| Month | Measure sum |
| January | 10 |
| Februrary | 20 |
| March | 30 |
| April | 25 |
| May | 47 |
| June | 2 |
Any tips? Basically I have this: SUMX('Table two','Table two'[Values]), but i need something to say "not in pivot month".
Hi CarlsBerg999
I think your calculate logic is as below:
If month<6, sum(Table1 value)+count(table2 value)
If month>=6, sum(Table1 value)-count(table2 value).
I build two tables like yours to have a test.
Table1:
Table2:
I build a calendar table and build a measure to achieve your goal.
Date Table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))Measure :
Measure = VAR _Count = CALCULATE ( COUNT ( Table1[Date] ), FILTER ( Table1, Table1[Date].[MonthNo] = MAX ( 'Date'[Month] ) ) ) VAR _Sum = SUMX ( FILTER ( 'Table2', Table2[Date].[MonthNo] = MAX ( 'Date'[Month] ) && Table2[Date].[MonthNo] <= 6 ), 'Table2'[Values] ) RETURN IF ( MAX ( 'Date'[Month] ) < 6, _Sum + _Count, _Sum - _Count )Result:
If this reply still couldn't help you solve your problem, please show me more details about your calculate logic.
In Table1, month =5 the result of count rows = 3, how can we get 47 in Measure sum. Or you can share your pbix file with me by your Onedrive for Business.
You can download the pbix file from this link:Power BI counting issue
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.
5 Replies
- amitchandak
Super User
CarlsBerg999 , A new column in table 2
Table2[Values] - Countx(filter(Table1 ,format(Table1[Date],"YYYYMM") <> format(Table2[Date],"YYYYMM")),Table1[Values])
CarlsBerg999Hi,
Im actually doing the measure in Excel Power Pivot, so it needs to be a measure rather than a new column. Therefore a new column is not an option in this context. Is it possible to do this in a measure?
- amitchandak
CarlsBerg999 , You need to have a common table for date/month. Try a measure like
sum(Table2[Values]) - Countx(filter(Table1 ,format(Table1[Date],"YYYYMM") <> format(max(Table2[Date],"YYYYMM"))),Table1[Values])
Hi CarlsBerg999
I think your calculate logic is as below:
If month<6, sum(Table1 value)+count(table2 value)
If month>=6, sum(Table1 value)-count(table2 value).
I build two tables like yours to have a test.
Table1:
Table2:
I build a calendar table and build a measure to achieve your goal.
Date Table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))Measure :
Measure = VAR _Count = CALCULATE ( COUNT ( Table1[Date] ), FILTER ( Table1, Table1[Date].[MonthNo] = MAX ( 'Date'[Month] ) ) ) VAR _Sum = SUMX ( FILTER ( 'Table2', Table2[Date].[MonthNo] = MAX ( 'Date'[Month] ) && Table2[Date].[MonthNo] <= 6 ), 'Table2'[Values] ) RETURN IF ( MAX ( 'Date'[Month] ) < 6, _Sum + _Count, _Sum - _Count )Result:
If this reply still couldn't help you solve your problem, please show me more details about your calculate logic.
In Table1, month =5 the result of count rows = 3, how can we get 47 in Measure sum. Or you can share your pbix file with me by your Onedrive for Business.
You can download the pbix file from this link:Power BI counting issue
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.
Hi CarlsBerg999
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
