Forum Discussion
[Solved] Issues with creating a single table with multiple constraints
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"
)
CALCULATE(
SUM(generalLedgerEntries[amount]) * -1,
ALL(dimensionvalues),
generalLedgerAccounts[accountNumber] >= "4740" && generalLedgerAccounts[accountNumber] <= "4741",
dimensionValues[Department Number] >= "011" && dimensionValues[Department Number] <= "155"
)
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.
3 Replies
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/1447523Department 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.
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.
