Forum Discussion

shansenTrek's avatar
shansenTrek
Frequent Visitor
1 year ago
Solved

Employee Sales at All Locations

Hi Everyone,

 

I thought this would be an easy measure to create but it is not working the way I want...

 

I am trying to display total sales by employee for all stores that they work at, but when I filter to a specific store, I only want to see the employees that have worked at the store but still see total sales at all stores.  There are only a few employees who work at multiple locations.  I either get the entire list of employees or only the sales at the one filtered location.  I have tried multiple iterations of my "All Sales" measure.

 

Thank you in advance for your help.

 

Here are my two measures:

Total Sales = SUM( fSales[Sales] )
All Sales = CALCULATE( [Total Sales] , ALL( dStore[Store Name] ) )

 

Tables and table relationships

Visual of the table 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    Drag this measure to the visual

    All sales revised = if([Total sales]=blank(),blank(),[All sales])

  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hello shansenTrek,
    Thank you for reaching out to the Microsoft Fabric Community forum.

    I’ve reproduced your scenario using my sample data and was able to get the expected output where:

    • Only employees who worked at the selected store are shown, and
    • The "All Sales" column shows their total sales across all stores, even when a store filter is applied.

    I created a simple data model with 3 tables:

    • dEmployee (Employee details)
    • dStore (Store details)
    • fSales (Sales fact table)

    With relationships:

    • dEmployee[Employee ID] → fSales[Employee ID] (1:* Single)
    • dStore[Store Number] → fSales[Store Number] (1:* Single)

    The Measures I used:

    Measure 1: Total Sales (Respects Store Filter)

    Total Sales = SUM(fSales[Sales])

    Measure 2: All Sales (Ignores Store Filter on Store Table)

    All Sales =

CALCULATE(

    SUM(fSales[Sales]),

    REMOVEFILTERS(dStore)

)

    This setup allows the visual to:

    • Show only the employees that worked at the selected store,
    • But still display their total sales across all locations.

    I’m attaching the .pbix file for your reference so you can see the exact implementation.


    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    You will need to deactivate the relationships for that.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Drag this measure to the visual

    All sales revised = if([Total sales]=blank(),blank(),[All sales])

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hello shansenTrek,
    Thank you for reaching out to the Microsoft Fabric Community forum.

    I’ve reproduced your scenario using my sample data and was able to get the expected output where:

    • Only employees who worked at the selected store are shown, and
    • The "All Sales" column shows their total sales across all stores, even when a store filter is applied.

    I created a simple data model with 3 tables:

    • dEmployee (Employee details)
    • dStore (Store details)
    • fSales (Sales fact table)

    With relationships:

    • dEmployee[Employee ID] → fSales[Employee ID] (1:* Single)
    • dStore[Store Number] → fSales[Store Number] (1:* Single)

    The Measures I used:

    Measure 1: Total Sales (Respects Store Filter)

    Total Sales = SUM(fSales[Sales])

    Measure 2: All Sales (Ignores Store Filter on Store Table)

    All Sales =

CALCULATE(

    SUM(fSales[Sales]),

    REMOVEFILTERS(dStore)

)

    This setup allows the visual to:

    • Show only the employees that worked at the selected store,
    • But still display their total sales across all locations.

    I’m attaching the .pbix file for your reference so you can see the exact implementation.


    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.