Ignore Dimension to give median total

[jasemilly]

I have a simple fact table to show when someone has created a job.

It has Date, Persons, Name, and a key field.

I would like to show the Total Number of jobs a person has created and a median measure for all Persons so can see how that person comprare to the median, I created this measure and using the persons name from the fact table AgentCreated and it works perfectly giving the median value.

VAR PersonTotals =
    SUMMARIZE (
           ALL ( AgentCreated[CreatedBy] ),       -- all persons in current date range
        AgentCreated[CreatedBy],
        "TotalPerPerson", countrows(AgentCreated) ) -- total qty for that person
    
RETURN
    MEDIANX ( PersonTotals, [TotalPerPerson] )

I have a DimAgents table that has a relationship with the FactagentCreated using the CreatedBy field and a stand date dimension table relationship on the CreatedDate.

Now when I put a slicer on the page using the Name from the DimAgents table and amend my measure to this. the measure doesn't return anything.

 SUMMARIZE (
        ALL ( DimAgents[Full Name] ),   // ignore agent slicer
        DimAgents[Full Name],
        "TotalPerPerson",
            CALCULATE (
                COUNTROWS ( AgentCreated ),
                KEEPFILTERS ( VALUES ( 'Date'[Date] ) ) // keep date slicer filter
            )
    )
RETURN
    MEDIANX ( PersonTotals, [TotalPerPerson] )

I don't know what part I am doing wrong.

Thanks for any help

[v-venuppu]

Hi @jasemilly ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu]

Hi @jasemilly ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FBergamaschi @Greg_Deckler for the prompt response.

Yes, filtering the DimAgents table to only include the 2 agents who have created jobs is the right approach. Apply a slicer on DimAgents[FullName] to select those 2 agents, and your DAX measure will then calculate the median based only on their job counts. This will prevent blanks or zeros from agents who haven’t created jobs.

Thank you.

[jasemilly]

here is how the agentcreated looks

CreatedDate, CreatedDateTime,CreatedBy, Key, JobentryTypedId, ShiftType
28/09/2024, 28/09/2024 07:01 , Jason, 500, 1 , 0

28/09/2024, 28/09/2024 08:01, Jason, 550,1,0

28/09/2024, 28/09/2024 11:00, Jason, 560,1,0

28/09/2024, 28/09/2024 08:01, Ian, 505,1,0
28/09/2024, 28/09/2024 08:10, Ian, 506,1,0

28/09/2024, 28/09/2024 08:15, Ian, 600,1,0

28/09/2024, 28/09/2024 08:20, Ian, 605,1,0
28/09/2024, 28/09/2024 08:21, Fred, 705,1,0

28/09/2024, 28/09/2024 08:30, Fred, 707,1,0

 
DimAgent would be

Name, FullName

Fred, Fred Smith

Ian, Ian Rush

Jason, Jason Walker

[FBergamaschi · Accepted solution]

Hello

I replicated the scenario and it works, there were some issues in the names.

This is the result I have

My pbix is here, based on your data

https://drive.google.com/drive/folders/1qgE_Uqk7LRJzRf_WXHl-0W91Guwe21M6?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[jasemilly]

Thanks for this, works great in test.  However I am getting a blank on my actual report and I was thinking that it's because I had lots of agents that hadn't created anything, so I altered my PQ to filter for 2 agents that had created and it still shows 0 for the median on my report.

maybe I need to filter the dimension table to the same 2 people as well.

[Greg_Deckler]

@jasemilly Can you post sample data?