Different results based on organisational levels

[BoAndersen]

Hi 

I'm having trouble with getting my measure right. 

At my company we have internal sales, that I want to include on oganisational levels below and axclude on oganisational levels above. 

- When Internal sales is yes on Group level, I don't want to include it in my total for the Group.

- When Internal sales is yes on Division level, I don't want to include it in my total for the Division.

- When Internal sales is yes on Center level, I don't want to include it in my total for the Center.

- When Internal sales is yes on Department level, I don't want to include it in my total for the Department.

I'm looking for a ay to show the sales based on the level I'm looking at - knowing that the sales wont add up to the total.

Is there a way around this?

Group Division Center Department Sales Group_Internal_Sales Division_Internal_Sales Center_Internal_Sales Department_Internal_Sales
G1 D01 C001 D0001 43242 Yes Yes No No
G1 D01 C001 D0002 24242 No No No No
G1 D01 C002 D0003 4442 No No No No
G1 D01 C003 D0004 242242 No No No No
G1 D02 C004 D0005 4424 No No No No
G1 D03 C005 D0006 53636 Yes No No No
G1 D03 C006 D0007 2242 No No No No
G1 D03 C007 D0008 442243 No No No No
G1 D04 C008 D0009 22342 Yes Yes Yes No
G1 D04 C008 D0010 442 No No No No
G1 D04 C009 D0011 6454664 No No No No
G1 D04 C010 D0012 645645 Yes Yes Yes Yes
G1 D04 C010 D0012 65564 No No No No
G1 D05 C011 D0013 787 No No No No
G1 D05 C012 D0014 783 No No No No

I’ve tried this:

Total Sales (Excluding Internal By Level) =

VAR _group = ISINSCOPE(SalesData[Group])

VAR _division = ISINSCOPE(SalesData[Division])

VAR _center = ISINSCOPE(SalesData[Center])

VAR _department = ISINSCOPE(SalesData[Department])

RETURN

SWITCH(

    TRUE(),

    -- Department level: exclude only if Department_Internal_Sales is Yes

    _department,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Department_Internal_Sales] <> "Yes"

        ),

    -- Center level: exclude only if Center_Internal_Sales is Yes

    _center,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Center_Internal_Sales] <> "Yes"

        ),

    -- Division level: exclude only if Division_Internal_Sales is Yes

    _division,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Division_Internal_Sales] <> "Yes"

        ),

    -- Group level: exclude only if Group_Internal_Sales is Yes

    _group,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Group_Internal_Sales] <> "Yes"

        ),

    -- Fallback: sum all

    SUM(SalesData[Sales])

)

[v-venuppu · Accepted solution]

Hi @BoAndersen ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FBergamaschi for the prompt response.

I have tried replicating the scenario using sample data.Please go through the attached PBIX file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly.

Thank you.

[BoAndersen]

I've added it to the initial post

[BoAndersen]

Group                Division             Center                Department     Sales                   Group_Internal_Sales                             Division_Internal_Sales            Center_Internal_Sales               Department_Internal_Sales

G1                        D01                     C001                   D0001                 43242                 Yes                     Yes                      No                             No

G1                        D01                     C001                   D0002                 24242                 No                      No                       No                             No

G1                        D01                     C002                   D0003                 4442                    No                      No                       No                             No

G1                        D01                     C003                   D0004                 242242               No                      No                       No                             No

G1                        D02                     C004                   D0005                 4424                    No                      No                       No                             No

G1                        D03                     C005                   D0006                 53636                 Yes                     No                       No                             No

G1                        D03                     C006                   D0007                 2242                    No                      No                       No                             No

G1                        D03                     C007                   D0008                 442243               No                      No                       No                             No

G1                        D04                     C008                   D0009                 22342                 Yes                     Yes                      Yes                             No

G1                        D04                     C008                   D0010                 442                      No                      No                       No                             No

G1                        D04                     C009                   D0011                 6454664             No                      No                       No                             No

G1                        D04                     C010                   D0012                 645645               Yes                     Yes                      Yes                             Yes

G1                        D04                     C010                   D0012                 65564                 No                      No                       No                             No

G1                        D05                     C011                   D0013                 787                      No                      No                       No                             No

G1                        D05                     C012                   D0014                 783                      No                      No                       No                             No

I’ve tried this:

Total Sales (Excluding Internal By Level) =

VAR _group = ISINSCOPE(SalesData[Group])

VAR _division = ISINSCOPE(SalesData[Division])

VAR _center = ISINSCOPE(SalesData[Center])

VAR _department = ISINSCOPE(SalesData[Department])

RETURN

SWITCH(

    TRUE(),

    -- Department level: exclude only if Department_Internal_Sales is Yes

    _department,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Department_Internal_Sales] <> "Yes"

        ),

    -- Center level: exclude only if Center_Internal_Sales is Yes

    _center,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Center_Internal_Sales] <> "Yes"

        ),

    -- Division level: exclude only if Division_Internal_Sales is Yes

    _division,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Division_Internal_Sales] <> "Yes"

        ),

    -- Group level: exclude only if Group_Internal_Sales is Yes

    _group,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Group_Internal_Sales] <> "Yes"

        ),

    -- Fallback: sum all

    SUM(SalesData[Sales])

)

[FBergamaschi]

Can you include the table in a text format so we can copy it in Power BI? 

And, can you show us what you have already in Power BI as a visual and where the issue is in Power BI snapshots?

Best

[BoAndersen]

Group Division Center Department Sales Group_Internal_Sales Division_Internal_Sales Center_Internal_Sales Department_Internal_Sales
G1 D01 C001 D0001 43242 Yes Yes No No
G1 D01 C001 D0002 24242 No No No No
G1 D01 C002 D0003 4442 No No No No
G1 D01 C003 D0004 242242 No No No No
G1 D02 C004 D0005 4424 No No No No
G1 D03 C005 D0006 53636 Yes No No No
G1 D03 C006 D0007 2242 No No No No
G1 D03 C007 D0008 442243 No No No No
G1 D04 C008 D0009 22342 Yes Yes Yes No
G1 D04 C008 D0010 442 No No No No
G1 D04 C009 D0011 6454664 No No No No
G1 D04 C010 D0012 645645 Yes Yes Yes Yes
G1 D04 C010 D0012 65564 No No No No
G1 D05 C011 D0013 787 No No No No
G1 D05 C012 D0014 783 No No No No

I've tried this:

Total Sales (Excluding Internal By Level) =

VAR _group = ISINSCOPE(SalesData[Group])

VAR _division = ISINSCOPE(SalesData[Division])

VAR _center = ISINSCOPE(SalesData[Center])

VAR _department = ISINSCOPE(SalesData[Department])

RETURN

SWITCH(

    TRUE(),

    -- Department level: exclude only if Department_Internal_Sales is Yes

    _department,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Department_Internal_Sales] <> "Yes"

        ),

    -- Center level: exclude only if Center_Internal_Sales is Yes

    _center,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Center_Internal_Sales] <> "Yes"

        ),

    -- Division level: exclude only if Division_Internal_Sales is Yes

    _division,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Division_Internal_Sales] <> "Yes"

        ),

    -- Group level: exclude only if Group_Internal_Sales is Yes

    _group,

        CALCULATE(

            SUM(SalesData[Sales]),

            SalesData[Group_Internal_Sales] <> "Yes"

        ),

    -- Fallback: sum all

    SUM(SalesData[Sales])

)