Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nlenzi
Frequent Visitor

Summarizing daily data into monthly totals

Hello all,

I am very new to PowerBI and DAX. I have some backround with Excel but that has not seemed to help me so far.

I am extremely impressed with this tool and praying I can make it work for my company... Here is what I've got and would appreciate any help I can get.

 

I have a CSV File:

CSV.jpg

 

I have created a row to summarize Type into two types PM or RM. Now I need to change this from daily to monthly infromation for the sake of my graph. So I formatted the date to month and year so what I have in PowerBI is this:

PBIData.jpg

 

How do I summarize my data? I need my table to be a sum of Actual Hours for Carol Stream's PMs In January 2016, then Feburary and so on. With no SUMIFS, I am not sure how to go about this.

If there is any more inforation I can provide please let me know.

1 ACCEPTED SOLUTION
AlexChen
Employee
Employee

I suggest you splitting column “complete” into 2 columns “complete month” and “complete year”.

For example: complete month -> January, complete year -> 2016

 

Then you can get the summarized actual hours you mentioned using code below.

 

SUMMARIZECOLUMNS(
yourTable[Repair center Name],
yourTable[Type],
yourTable[Complete month],
yourTable[Complete year],
"sum hours", CALCULATE(sumx(yourTable, yourTable[Actual Hours]))

)

 

If you just want the sum hours of PM, you can add a filter:

 

SUMMARIZECOLUMNS(
yourTable[Repair center Name],
yourTable[Type],
yourTable[Complete month],
yourTable[Complete year],
FILTER(yourTable, yourTable[Type] = "PM"),
"sum hours", CALCULATE(sumx(yourTable, yourTable[Actual Hours]))

)

 

As @Habib said, drillthrough is not supported in line chart currently, please try to use column chart to enable drillthrough, or you can put Month field only on X-axis to view the Month level data.

 

View solution in original post

4 REPLIES 4
inderz
Helper I
Helper I

This is closet to a problem I am struggling with !

 

I have an Excel Sheet of dates of Machine In for Repair and out from repair for different machines.

 

Format is as below :-

 

2017-06-10 09_18_25-Eqpt & Veh in out details.xlsx - Microsoft Excel.png

I need to create a Table to display Avg Repair taken for EACH Machine ID. 

 

Also I need to calculate, display and export, Mean Time Between Failure (MTBF) for each type of Machine.

 

Any ideas plz ?

 

Thx

Inder

AlexChen
Employee
Employee

I suggest you splitting column “complete” into 2 columns “complete month” and “complete year”.

For example: complete month -> January, complete year -> 2016

 

Then you can get the summarized actual hours you mentioned using code below.

 

SUMMARIZECOLUMNS(
yourTable[Repair center Name],
yourTable[Type],
yourTable[Complete month],
yourTable[Complete year],
"sum hours", CALCULATE(sumx(yourTable, yourTable[Actual Hours]))

)

 

If you just want the sum hours of PM, you can add a filter:

 

SUMMARIZECOLUMNS(
yourTable[Repair center Name],
yourTable[Type],
yourTable[Complete month],
yourTable[Complete year],
FILTER(yourTable, yourTable[Type] = "PM"),
"sum hours", CALCULATE(sumx(yourTable, yourTable[Actual Hours]))

)

 

As @Habib said, drillthrough is not supported in line chart currently, please try to use column chart to enable drillthrough, or you can put Month field only on X-axis to view the Month level data.

 

nlenzi
Frequent Visitor

So I swithched it to a table and through a Hierachy I get exactly what I want:

BItable.png

But when I switch to a line chart the hierachy only has year available, why can I not get month?

Habib
Responsive Resident
Responsive Resident

When you place date filed in line chart, it picks date hierarchy by default and shows year only. Drilldown is not available in line chart currently. You need change your filed setting to use actual date value instead of date hierarchy.

To get this done, right click on your date field in visualization section and change. Refer to below image.

month.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.