Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Community,
Excuse the fact that I might be posting this with a possible solution floating around the community, I've tried to use different solutions found around the community but none seem to fully cover the requirement I have, either that or I may not be approaching this issue correctly. In any case, I have a table that represents every project that is ongoing or completed, alongside that is a column that represents the number of active days per project (in the project's lifecycle is a total number of active days for that project as well as a number of days where the project was idle/on-hold). From this column I'd like to calculate the total average based on selected values filtered by slicers and compare whether the project's number of active days is greater or less than the generated average.
Below is an example of what I'm trying to achieve, the representation might be lackluster but:
Input:
Output would be:
If filtered for market 'A'
In this case since the selected filter is for Market 'A' the avg is computed based on the values extracted for this filter and generates each project's status based on the comparison between that average and the project's # of Active Days, I would like to be able to implement this either based on a Market slicer selection or Customer slicer selection. I've pretty much managed to nail the logic for the status and comparison and at one point event the average but strictly on a market basis but can't seem to find the right way to compute the average to adjust based on selected market or customer slicer.
Any help or suggested solutions are greatly appreciated.
Best,
Sam
Solved! Go to Solution.
Hi @samnf
Here is a sample file with the solution https://we.tl/t-YYoDKxcRHL
Average Days =
CALCULATE (
AVERAGE ( Projects[Active Days] ),
ALLSELECTED ()
)
Status =
IF (
SELECTEDVALUE ( Projects[Active Days] ) <= [Average Days],
"On Time",
"Late"
)
Hi @samnf
Here is a sample file with the solution https://we.tl/t-YYoDKxcRHL
Average Days =
CALCULATE (
AVERAGE ( Projects[Active Days] ),
ALLSELECTED ()
)
Status =
IF (
SELECTEDVALUE ( Projects[Active Days] ) <= [Average Days],
"On Time",
"Late"
)
Hello @tamerj1 ,
Firstly, really appreciate the timely response, the solution you provided worked effectively, turns out I was creating the right measure for the average but created a custom column for the comparison and failed to use the SelectedValue function.
Once again, thank you for your time!
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |