topic Different results based on organisational levels in DAX Commands and Tips

Different results based on organisational levels

BoAndersen — Fri, 27 Jun 2025 10:57:48 GMT

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

)

Re: Different results based on organisational levels

FBergamaschi — Fri, 27 Jun 2025 09:17:22 GMT

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

Re: Different results based on organisational levels

BoAndersen — Fri, 27 Jun 2025 10:29:36 GMT

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

)

Re: Different results based on organisational levels

BoAndersen — Fri, 27 Jun 2025 10:44:24 GMT

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

)

Re: Different results based on organisational levels

BoAndersen — Fri, 27 Jun 2025 10:58:21 GMT

I've added it to the initial post

Re: Different results based on organisational levels

v-venuppu — Tue, 01 Jul 2025 12:16:48 GMT

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.