[Solved] Issues with creating a single table with multiple constraints

[DCrone]

Solution

OPSTAT Retail Income = 
IF(
    MAX(dimensionValues[Department Number]) = "155",
    CALCULATE(
        SUM(generalLedgerEntries[amount]) * -1,
        REMOVEFILTERS(dimensionValues[Department Name]),
        generalLedgerAccounts[accountNumber] >= "4740" && generalLedgerAccounts[accountNumber] <= "4741",
        dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "155"
    )
    +
    CALCULATE(
        SUM(generalLedgerEntries[amount]) * -1,
        generalLedgerAccounts[accountNumber] >= "4700" && generalLedgerAccounts[accountNumber] <= "4705",
        dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "029" ||
        dimensionValues[Department Number] >= "040" && dimensionValues[Department Number] <= "155"
    ),

    CALCULATE(
        SUM(generalLedgerEntries[amount]) * -1,        
        generalLedgerAccounts[accountNumber] >= "4700" && generalLedgerAccounts[accountNumber] <= "4705",
        dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "029" ||
        dimensionValues[Department Number] >= "040" && dimensionValues[Department Number] <= "155"
    )
)

-----Topic Question-----
I am trying to recreate a report that has separate filters, in a single table. The first calculate statement works as expected, but I need the second to aggregate all the listed values and place them only on department "155".

The logic being:
For departments "011" through "154", sum the accounts between "4700" and "4705"
For departments "011" through "155", sum the accounts between "4740" and "4741" and aggregate to a single value of department "155"

I've tried to use IF statements, SWITCH statements, but it always shows an error saying column not found. When I attempt to use ALL(), it aggregates it correctly, but I cant isolate the it to just show on row department "155". I'm sure there is a best practice for this, but I can't figure it out.

Retail Income =
CALCULATE(
    SUM(generalLedgerEntries[amount]) * -1,
    dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "029" ||
    dimensionValues[Department Number] >= "040" && dimensionValues[Department Number] <= "154",
    generalLedgerEntries[accountNumber] >= "4700" && generalLedgerEntries[accountNumber] <= "4705"
)

+

CALCULATE(
    SUM(generalLedgerEntries[amount]) * -1,
    generalLedgerAccounts[accountNumber] >= "4740" && generalLedgerAccounts[accountNumber] <= "4741",
    dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "155"
)

Using ALL gives me the correct value for row department "155", but departments "011" through "154" also adds it to each associated department giving me incorrect results. I can't seem to return the value for specifically department "155"

CALCULATE(
    SUM(generalLedgerEntries[amount]) * -1,
    ALL(dimensionvalues),
    generalLedgerAccounts[accountNumber] >= "4740" && generalLedgerAccounts[accountNumber] <= "4741",
    dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "155"
)

Picture to explain the logic more clearly

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[DCrone]

Department	Sum accounts 4700-4705	Sum accounts 4740 - 4741	Expected Output
011	200	15	200
013	300	10	300
014	300	20	300
015	250	25	250
017	150	10	150
155		580	660

Above is sample data to illustrate what I am trying to achieve. The expected output consists of departments 011-154 containing their respective total sum of accounts 4700-4705, while for department 155, I want to sum the entire column of accounts 4740-4741 and have it displayed on department 155.

660 in this example is the sum of all the accounts 4740-4741 inclusive of department 155's value where as all the other values are just the sum of the accounts 4700-4705.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = if(AND(1*Data[Department]>=11,1*Data[Department]<=17),Data[Sum accounts 4700-4705],SUM(Data[Sum accounts 4740 - 4741]))

Hope this helps.