Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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:
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.
Solved! Go to Solution.
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.
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 :-
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
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.
So I swithched it to a table and through a Hierachy I get exactly what I want:
But when I switch to a line chart the hierachy only has year available, why can I not get month?
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.