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 Everybody. I'm looking for ideas/methodology for a problem we have involving a dynamic baseline and assessment period, as well as a dynamic unit of measure (UOM). To explain this further, we want to be able to define and select two different time periods (called baseline and assessment period), over which values need to be averaged/summed dynamically. Then we would also like to be able to dynamically select a UOM, which would also affect the values returned by the baseline and assessment period. The source data looks something like as follows:
Transaction Table
Date(dd/mm/yyyy) | Value | UOM
01/02/2018 | 30 | m3
02/02/2018 | 40 | m3
03/02/2018 | 50 | m3
04/02/2018 | 10 | m3
05/02/2018 | 20 | m3
UOM Table
UOM | BASE UOM | Conversion factor
m3 m3 1
ltr m3 1000
What we are looking for is to be able to select the dynamic baseline and assessment periods (from a slicer or something similar) as well as the UOM, and the resulting table would look like the following for selecting Baseline as (01/02/2018-02/02/2018)
and Assessment Period as 03/02/2018 and UOM as m3.
Baseline Average | Assessment Period Average
35 | 50
If we were to select Baseline as (01/02/2018-05/02/2018) and Assessment Period as (04/02/2018-05/02/2018) and UOM as ltr, then we would expect:
Baseline Average | Assessment Period Average
30000 | 15000
Is something like this possible? Do we need to make any changes to the underlying data (such as adding columns)?
Thank you.
Daniel.
Solved! Go to Solution.
Hi @danielfynes
I came up with this idea and just got around to posting it.
It could be overcomplicating things depending on your requirements....
// This is the core calculation that averages Value over rows of Transaction table
// in the selected UOM Average Final UOM = AVERAGEX ( 'Transaction', 'Transaction'[Value] * CALCULATE ( MIN ( UOM[Conversion Factor] ) ) ) // Assessment Period Average directly references Average Final UOM // Could merge this measure and previous measure into one Assessment Period Average = [Average Final UOM] // Baseline Average performs the same calculation but using with
// Assessment Date relationship activated Baseline Average = CALCULATE ( [Average Final UOM], ALL ( 'Assessment Date' ), USERELATIONSHIP ( 'Assessment Date'[Assessment Date], 'Baseline Date'[Baseline Date] ) )
Then you can create a report page like this:
Regards
Owen
Hi @danielfynes
I came up with this idea and just got around to posting it.
It could be overcomplicating things depending on your requirements....
// This is the core calculation that averages Value over rows of Transaction table
// in the selected UOM Average Final UOM = AVERAGEX ( 'Transaction', 'Transaction'[Value] * CALCULATE ( MIN ( UOM[Conversion Factor] ) ) ) // Assessment Period Average directly references Average Final UOM // Could merge this measure and previous measure into one Assessment Period Average = [Average Final UOM] // Baseline Average performs the same calculation but using with
// Assessment Date relationship activated Baseline Average = CALCULATE ( [Average Final UOM], ALL ( 'Assessment Date' ), USERELATIONSHIP ( 'Assessment Date'[Assessment Date], 'Baseline Date'[Baseline Date] ) )
Then you can create a report page like this:
Regards
Owen
Hi @danielfynes,
It is possible to make a dynamic visual based on selection but it is impossible to create dynamic calculated column.
You can create a slicer with UOM as source column and write a measure to get selected value from UOM table, then use this measure as calculated parameter with baseline and assessment calculation.
Reference link:
Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.
Regards,
Xiaoxin sheng
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 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |