Filter Context Confusion and use of CALCULATE

Purplehazed

New Member
Joined
Jul 7, 2014
Messages
7
In PowerBI I am creating some personal spending metrics. One is an average cost per meal. I am trying to
build a table the shows amount spent on grocery and an average $ per meal.

I am struggling with a measure that calculates the days in a month that will be the denominator in the $ per meal calculation.

In the pivot table pasted below and am getting 67 from the measure below. I thought that using CALCULATE in the measure
would prevent getting the same result each month i.e. the pivot tables filter would control the filter context.


I expected 28 days for Feb, 31 days for Mar and 26 days for Apr and April would go up a day each day until the end of the month then start over with May.

My date table example skips a few days so ignore 67, that number should be more like 85.


Any thoughts are appreciated!




Row Labels
Sum of Amount Test
February(775)67
March(898)67
April(509)67
Grand Total(2,181)67

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Test := CALCULATE(COUNTROWS(FILTER('Date','Date'[Today]>'Date'[Date])))
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Purplehazed

New Member
Joined
Jul 7, 2014
Messages
7
I think I figured out the problem.

I built a simple two table model to be used to ask the question. In a measure I used CALCULATE correctly (I think) but did not get the expected result. When I added a "month" column via a calculated column in the lookup date table and pulled that column into the pivot table I got this (below).

Row LabelsSum of Amount Test
2(775)20
3(898)27
4(509)20
Grand Total(2,181)67

<colgroup><col><col><col></colgroup><tbody>
</tbody>



Any explanation of what I've done and/or not done correctly in non-programmer language would still be much appreciated.

I am determined to learn DAX!!!! Very powerful!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,835
Messages
5,544,592
Members
410,621
Latest member
S Oberlander
Top