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.
I have a very simple dataset with two columns. Instead of just taking the average (in the case below.. 9.4), I would like to take the averages of each ProcName and THEN average those together. (in the case below.. 9.167).
ProcName | TotalMilliseconds |
Proc1 | 5 |
Proc1 | 10 |
Proc2 | 8 |
Proc3 | 4 |
Proc3 | 20 |
Solved! Go to Solution.
Hi @djanszentql ,
Attached file with calculation.
Here is the result:
Average per product =
VAR _productAvg =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[ProcName] ),
"_Average", CALCULATE ( AVERAGE ( 'Table'[TotalMilliseconds] ) )
)
RETURN
IF (
HASONEVALUE ( 'Table'[ProcName] ),
AVERAGE ( 'Table'[TotalMilliseconds] ),
AVERAGEX ( _productAvg, [_Average] )
)
Regards,
Nemanja Andic
Hi @djanszentql ,
Attached file with calculation.
Here is the result:
Average per product =
VAR _productAvg =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[ProcName] ),
"_Average", CALCULATE ( AVERAGE ( 'Table'[TotalMilliseconds] ) )
)
RETURN
IF (
HASONEVALUE ( 'Table'[ProcName] ),
AVERAGE ( 'Table'[TotalMilliseconds] ),
AVERAGEX ( _productAvg, [_Average] )
)
Regards,
Nemanja Andic
If I wanted to add a filter on date such as this:
Hi,
If it will be static filter (always Max date - 29 days) you can create calculate column (in my example "DatesBetween" -data type WholeNumber) and use this column to either filter data staticly or dynamically inside measure calculation.
Example static: you can add this new calculated column to Filters pane (filter for whole page, filter for whole report, filter for visualization) and to define which DatesBetween value to display.
Example dynamic:
On DAX formula from previous reply, you can add new condition using FILTER function.
Ie: calculate(average(TotalMiliseconds), filter(table, DatesBetween > 0))
If it doesn't help could you send pbix file with dummy data, but with same structure as as (same tables, relationships, data types).
Regards,
Nemanja Andic
It will be static so this should work!! Thanks for all your help!
One last question I have (and please let me know if I should make a separate post for this because I keep expanding the scope of the initial question for this thread).. How could I turn this into a weighted average where the third column is ExecutionCount. To clarify, if Proc1 had 10,000 exeuctions, but Proc2 had 5,000.. I would want to apply more weight to Proc1 in the average calculation. Again, please let me know if I should make a separate post for this. I appreciate your help thus far!
You can keep it here as well, until we find complete solution 🙂
Could you just send expected results in Excel format (no need to upload file to dropbox/google drive, just paste here dummy values)? When i see dummy data and expected results it will be easier to reply.
It won't let me attach the excel file, but here are the values/calculation:
Thanks for provided details, attached updated version of the pbix file.
Weighted Average Measure =
VAR _Sumproduct =
SUMX ( 'Table', 'Table'[TotalMilliseconds] * 'Table'[ExecutionCount] )
VAR _Sum =
SUM ( 'Table'[ExecutionCount] )
RETURN
DIVIDE ( _Sumproduct, _Sum )
Regards,
Nemanja Andic
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |