Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I believe I will need a push in the right direction to calculate, compare and summarize the output of several generators per time period.
Raw Data
Date | Project | Device No | Output |
25-Sep | Sydney | 1 | 2310 |
25-Sep | Sydney | 2 | 2400 |
25-Sep | Sydney | 3 | 2430 |
25-Sep | Sydney | 4 | 2390 |
25-Sep | Sydney | 5 | 2420 |
25-Sep | Sydney | 6 | 2380 |
25-Sep | Brisbane | 1 | 1860 |
25-Sep | Brisbane | 2 | 1890 |
25-Sep | Brisbane | 3 | 1730 |
25-Sep | Brisbane | 4 | 1830 |
26-Sep | Sydney | 1 | 2100 |
26-Sep | Sydney | 2 | 2110 |
26-Sep | Sydney | 3 | 2080 |
26-Sep | Sydney | 4 | 2090 |
26-Sep | Sydney | 5 | 2100 |
26-Sep | Sydney | 6 | 2120 |
26-Sep | Brisbane | 1 | 1600 |
26-Sep | Brisbane | 2 | 1610 |
26-Sep | Brisbane | 3 | 1680 |
26-Sep | Brisbane | 4 | 1660 |
Step 1
For each time period (in this example day) I want to compare the output of each generator with the median of all generators at that location.
If the individual output is above the median, I deem that generator as fully available (=1). If below, it is deemed as not available (=0).
Date | Project | Device No | Output | Median per project per day | Above Median |
25-Sep | Sydney | 1 | 2310 | 2395 | 0 |
25-Sep | Sydney | 2 | 2400 | 2395 | 1 |
25-Sep | Sydney | 3 | 2430 | 2395 | 1 |
25-Sep | Sydney | 4 | 2390 | 2395 | 0 |
25-Sep | Sydney | 5 | 2420 | 2395 | 1 |
25-Sep | Sydney | 6 | 2380 | 2395 | 0 |
25-Sep | Brisbane | 1 | 1860 | 1845 | 1 |
25-Sep | Brisbane | 2 | 1890 | 1845 | 1 |
25-Sep | Brisbane | 3 | 1730 | 1845 | 0 |
25-Sep | Brisbane | 4 | 1830 | 1845 | 0 |
26-Sep | Sydney | 1 | 2100 | 2100 | 1 |
26-Sep | Sydney | 2 | 2110 | 2100 | 1 |
26-Sep | Sydney | 3 | 2080 | 2100 | 0 |
26-Sep | Sydney | 4 | 2090 | 2100 | 0 |
26-Sep | Sydney | 5 | 2100 | 2100 | 1 |
26-Sep | Sydney | 6 | 2120 | 2100 | 1 |
26-Sep | Brisbane | 1 | 1600 | 1635 | 0 |
26-Sep | Brisbane | 2 | 1610 | 1635 | 0 |
26-Sep | Brisbane | 3 | 1680 | 1635 | 1 |
26-Sep | Brisbane | 4 | 1660 | 1635 | 1 |
Step 2
Now I need to sum up these numbers.
Project | Device No | Sum of Above Median |
Sydney | 1 | 1 |
Sydney | 2 | 2 |
Sydney | 3 | 1 |
Sydney | 4 | 0 |
Sydney | 5 | 2 |
Sydney | 6 | 1 |
Brisbane | 1 | 1 |
Brisbane | 2 | 1 |
Brisbane | 3 | 1 |
Brisbane | 4 | 1 |
In PowerBI I have calculated the median as follwoing:
Somehow my sumx measure median does not work across several rows.
Excel file with final result: https://www.dropbox.com/s/ru33h3wr2vkr6g5/CompareWMedian.xlsx?dl=0
PBIX with my approach: https://www.dropbox.com/s/qy1e4jm5ikc9byj/CompareWMedian.pbix?dl=0
Many thanks upfront.
Regards
Andreas
Solved! Go to Solution.
Hi there @Anonymous ,
This is what I have for you:
__ComparewithMedian =
SUMX(SUMMARIZE('Table','Table'[Date],'Table'[Device No], "c",
IF(SUM('Table'[Output]) >
CALCULATE(MEDIANX('Table', 'Table'[Output]), ALLEXCEPT('Table','Table'[Project], 'Table'[Date])), 1, 0)),[c])
As seen here:
Link to file.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi there @Anonymous ,
This is what I have for you:
__ComparewithMedian =
SUMX(SUMMARIZE('Table','Table'[Date],'Table'[Device No], "c",
IF(SUM('Table'[Output]) >
CALCULATE(MEDIANX('Table', 'Table'[Output]), ALLEXCEPT('Table','Table'[Project], 'Table'[Date])), 1, 0)),[c])
As seen here:
Link to file.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@Anonymous , You need values on some group by to make it work
Sumx = sumx(values(Output[project]) ,if(Sum(Output[Output])>[MedianPerProjectPerDay],1,0))
or create median as column not measure
Thanks @amitchandak ,
I tried this and a few other ideas, but am still stuck. Your provided measeure sums up the output and then compares it with the median. Therefore I will always only get 1 as maximum. I need to compare the individual output value with the median for that period, determine if it is 0 or 1 and then sum up all the 1s.
I also tried to do it via calculated column as
Which gives me te following error message: "Expressions that yield variant data-type cannot be used to define calculated columns."
Just to double check, I used AVERAGE, which seems to produce a result. But it seems the calculation is limited to the values within a row
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |