calculate number of records with filter

[bertusavius]

This is table A
It represents the total revenu of a certain revenue subgroup per month. this information is known.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]year[/TD]
[TD]month[/TD]
[TD]property[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]march[/TD]
[TD]500[/TD]
[TD]99999999[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]april[/TD]
[TD]600[/TD]
[TD]88888888[/TD]
[/TR]
</tbody>[/TABLE]

Table B
represents a database of different kinds of revenue with properties
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]property[/TD]
[TD]calculated column[/TD]
[/TR]
[TR]
[TD]1-6-2011[/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15-6-2011[/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The date column of this revenue table is related to a date table with calculated year and month names.

I'd like to evenly divide the entire revenue per month (which is know data) per row in the database of revenue.

In other words:
the calculated column should contain something like:
TableA[revenue per month]/number of records in table B of the same month, year and property

 

[bertusavius]

Perhaps I could clarify it further:

This is the table in which total revenue per month is stored:

	P	Q	R	S

<tbody>
[TD="align: center"]8[/TD]
[TD="bgcolor: #4F81BD"]ID[/TD]
[TD="bgcolor: #4F81BD"]year[/TD]
[TD="bgcolor: #4F81BD"]month[/TD]
[TD="bgcolor: #4F81BD"]Total revenu per month[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DCE6F1, align: right"]5800[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2012[/TD]
[TD="bgcolor: #DCE6F1"]may[/TD]
[TD="bgcolor: #FFFF00, align: right"] € 75.000,00[/TD]

</tbody>

Blad1​

After this figure is calculated at the end of the month, I would like to use it to evenly distribute this number over all the records that match this ID to come to this kind of result:

	V	W	X	Y	Z	AA
a1	may

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]
[TD="bgcolor: #4F81BD"]Product[/TD]
[TD="bgcolor: #4F81BD"]ID[/TD]
[TD="bgcolor: #4F81BD"]year[/TD]
[TD="bgcolor: #4F81BD"]month[/TD]
[TD="bgcolor: #4F81BD"]date[/TD]
[TD="bgcolor: #4F81BD"]calc-column[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DCE6F1"]a1[/TD]
[TD="bgcolor: #DCE6F1, align: right"]5800[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2012[/TD]
[TD="bgcolor: #DCE6F1"]may[/TD]
[TD="bgcolor: #DCE6F1, align: right"]5-5-2012[/TD]
[TD="bgcolor: #FFFF00, align: right"] € 25.000,00 [/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5800[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]10-5-2012[/TD]
[TD="bgcolor: #FFFF00, align: right"] € 25.000,00 [/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #DCE6F1"]a1[/TD]
[TD="bgcolor: #DCE6F1, align: right"]5800[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2012[/TD]
[TD="bgcolor: #DCE6F1"]may[/TD]
[TD="bgcolor: #DCE6F1, align: right"]11-5-2012[/TD]
[TD="bgcolor: #FFFF00, align: right"] € 25.000,00 [/TD]

</tbody>

Blad1
​

So the calculated record in AA9 would be would be:
S6/count of records that match the criteria of year, month and ID

 

[JavierGuillen]

Hello,

Assuming the first table is called [Table1] and the second one is [Table2], you could use a measure expression like:

=CALCULATE(
SUM( Table1[Total revenu per month] ),
FILTER( Table1, Table2[ID] = Table1[ID] ) ) /
CALCULATE(
COUNTROWS( ALL( Table2 ) ),
Table2[ID] = EARLIER( Table2[ID] )
)

The calculation only considers the ID, but if needed it can be easily modified to include month and year as part to lookup procedure.

 

[Laurent C]

I recommend you add the first day of the month to your first table. Then link this table to your date and to the dimension corresponding to the ID column.

You will then be able to create a calculated column with something like:
=CALCULATE(
SUM( YourSummaryTable[Total revenue per month]); PARALLELPERIOD('Date'[Date];0;MONTH)
)

 

[bertusavius]

@JavierGuillen:
=CALCULATE(
SUM( Table1[Total revenu per month] ),
FILTER( Table1, Table2[ID] = Table1[ID] ) ) /
CALCULATE(
COUNTROWS( ALL( Table2 ) ),
Table2[ID] = EARLIER( Table2[ID] )
)

The formula gives an error as soon as introduce Table2[ID]. I don't understand why.
Is it possible to use the second table in the filter?



@Laurent C
=CALCULATE(
SUM( YourSummaryTable[Total revenue per month]); PARALLELPERIOD('Date'[Date];0;MONTH)
)
This returns the complete value of the revenue in every record. It should divide this by the number of rows that match the ID, Year, Month criteria as to evenly divide the revenue over the total number of rows.



Again: I really appreciate your help so far.

 

[Laurent C]

@JavierGuillen:

@Laurent C
=CALCULATE(
SUM( YourSummaryTable[Total revenue per month]); PARALLELPERIOD('Date'[Date];0;MONTH)
)
This returns the complete value of the revenue in every record. It should divide this by the number of rows that match the ID, Year, Month criteria as to evenly divide the revenue over the total number of rows.

Yes. I focused on the "get the total of the expression for the current month" part. You can apply the same technique to count the number of days for the current month, then proceed with the division.