Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
samnf
Frequent Visitor

Comparison of column value to average of column total

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:

samnf_1-1651836352069.png

Output would be:
If filtered for market 'A'

samnf_2-1651836467357.png

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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"
)

1.png

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

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"
)

1.png

samnf
Frequent Visitor

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors