## PowerBi Matrix Subtotal Feature & Measures

Anything related to PowerPivot and DAX Formuale
okgo
Posts: 2
Joined: Mon May 21, 2018 10:24 pm

### PowerBi Matrix Subtotal Feature & Measures

Sample pbix
https://www.dropbox.com/s/j144i4hfe8k5su0/PowerBi%20Timesheet%203.pbix?dl=0

Screenshot
https://www.dropbox.com/s/5fdtvytb39ppcwy/TS3.JPG?dl=0

Can the measure [Forecast] be adjusted so the subtotal feature in the Matrix acts like an Excel pivot table? For example 2017-07 would be 45.34 not 78.67

Credit to Ashish_Mathur for input thus far

RamanaV
Posts: 12
Joined: Thu Oct 19, 2017 12:57 pm

### Re: PowerBi Matrix Subtotal Feature & Measures

Hi,

How did you define all the measures? I did not look at the dropbox .pbix file.

In DATESBETWEEN function, [Month till where data is available] is used twice. How come?

If you can clarify this, I will look at the output and try to answer your question.

okgo
Posts: 2
Joined: Mon May 21, 2018 10:24 pm

### Re: PowerBi Matrix Subtotal Feature & Measures

Thank you for your comments. I honestly don't know why that function is used twice (Start date & End date). I had help writing this. If we can get the sub total feature working - I then plan to go back and study every line of this until I full understand it.

4 x in the Calendar table

Code: Select all

`First month in which data is available = CALCULATE(MIN('Calendar'[Date]),FILTER(DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAXX(ALL('Calendar'),'Calendar'[Date])),[Hours Spent]>0))`

Code: Select all

`Month till where data is available = CALCULATE(Max('Calendar'[Date]),FILTER(DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAXX(ALL('Calendar'),'Calendar'[Date])),[Hours Spent]>0))`

Code: Select all

`Months elapsed = DATEDIFF(CALCULATE(MIN('Calendar'[Date]),FILTER(DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAX('Calendar'[Date])),[Hours Spent]>0)),MAX('Calendar'[Date]),MONTH)`

Code: Select all

`Year-month = YEAR('Calendar'[Date])&"-"&FORMAT(MONTH('Calendar'[Date]),"00")`

3 x in the main data section

Code: Select all

`3 months rolling average = if(ISBLANK([Hours Spent]),BLANK(),    if([Months elapsed]>=2,AVERAGEX(DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),MAX('Calendar'[Date])),[Hours Spent]),BLANK()))`

Code: Select all

`Forecast = if(MIN('Calendar'[Date])<[First month in which data is available],BLANK(),if(EOMONTH(MAX(Project_End_Date[End_Date]),0)>=EOMONTH(MAX('Calendar'[Date]),0),CALCULATE([3 months rolling average],DATESBETWEEN('Calendar'[Date],[Month till where data is available],[Month till where data is available])),BLANK()))`

Code: Select all

`Hours Spent = SUM(Data[Hrs])`