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

Postby okgo » Mon May 21, 2018 10:35 pm

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: 14
Joined: Thu Oct 19, 2017 12:57 pm

Re: PowerBi Matrix Subtotal Feature & Measures

Postby RamanaV » Tue May 22, 2018 3:09 pm

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

Postby okgo » Sat May 26, 2018 4:07 am

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])


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 7 guests

cron