Calculate ignoring filters in variables

[dobregon]

Hi guys,

I'm not sure if i can do this, i have checked and i haven't found an example that i could use. I have a database with this example info 

TypeName	TypeId	Item	ItemId	Value
Actual	1	Bike	1	500
Actual	1	Car	2	600
Actual	1	Bus	3	550
Actual	1	Others	4
Actual	1	Total	5	2000
Expected	2	Bike	1	300
Expected	2	Car	2	1000
Expected	2	Bus	3	500
Expected	2	Others	4
Expected	2	Total	5	2100

As you can see i have the Actuals and expected sales (imagine number of items) for each item in my market and lines with the total.

This is becasue the others can't be measured and for that we have the lines for each item that can be summarize and the total income in the market with that... we will be able to know the others with a simple difference, but in my real case it is more complecated.

And i want to calculate by differnece using a measure and this is the code that i have

Actuals = 
-- Create variables
var Bike = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 1 && table[TypeId] = 1))
var Car = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 2 && table[TypeId] = 1))
var Bus = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 3 && table[TypeId] = 1))
var Total = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 5 && table[TypeId] = 1))

-- Calculate Others
Var OthersCalculated = Total - (Bike + Car + Bus)
var Others = if(itemid = 4, OthersCalculated , 0)

Return
Bike + Car + Bus + Others + Total

With that i'm looking (and adding the same in expected) that the table give me something like this

TypeName	TypeId	Item	ItemId	Value	measure
Actual	1	Bike	1	500	500
Actual	1	Car	2	600	600
Actual	1	Bus	3	550	550
Actual	1	Others	4		350
Actual	1	Total	5	2000	2000
Expected	2	Bike	1	300	300
Expected	2	Car	2	1000	1000
Expected	2	Bus	3	500	500
Expected	2	Others	4		300
Expected	2	Total	5	2100	2100

The problem is that as all the variables used to calculate their values are filter by their item, when you calculate the others as the item others is not the same than the others the others calculation dont run and they applied in the rest of the calculations.

So, i'm looking if there is an option when i calculate the others to remove everthing and do the calculation without the itemid. i have tried and it is not running.

Maybe it is other function that i can use better but i dont know what to use.

Thanks and regards

[dobregon · Accepted solution]

My autoresponse that could help someone or me in the future 🤣, i have solved the problem doing this:

Actuals = 
-- Create variables
var Bike = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 1 && table[TypeId] = 1))
var Car = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 2 && table[TypeId] = 1))
var Bus = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 3 && table[TypeId] = 1))
var Total = CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 5 && table[TypeId] = 1))

-- Calculate Others (total - bike - Car - bus)
Var OthersCalculated = CALCULATE(
(
CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 5 && table[TypeId] = 1))
-
CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 1 && table[TypeId] = 1))
-
CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 2 && table[TypeId] = 1))
-
CALCULATE(SUM(Table[value]), filter(table, table[itemId] = 3 && table[TypeId] = 1))
)
, ALL(Table))

-- Only applied in Others
var Others = if(itemid = 4, OthersCalculated , 0)

Return
Bike + Car + Bus + Others + Total

The main important thing is that if i do the calculate with the variables, the all(table) doesn't affect to the values but if i do with the same code than the variables the All(table) will apply the calculation in all the items for that, i create a new variable that filter only for the item=4 and then as everthing is in their item and the rest have blanks or 0 i can return all the variables with a sum and everthing is correct.

Maybe it is not the best performance query but it works for the moment, but if anyone know other form to do that with a best patrice and performance it would be a great idea

[amitchandak]

I did get it completely. But I am assuming you are able to get other values as a new column.

Few changes you can do is you can use in {"1","2","3"} and can get data in one variable

I assume you are getting total not others, means binning and grouping will not help

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

use union and summarize to put this a new row. Not that you can use static values in summarizing. Also, you can use the filter in table name to filter data.

Summary = UNION(
SUMMARIZE('Table1','Table1'[Customer],'Table1'[created_date],'Table1'[DA Status],"Sales",sum('Table1'[Sales]),"Invoices",count('Table1'[ID]),"Time",DIVIDE(SUM('Table1'[Ship Time]),1),"Stage"," Ship","Gross",sum('Table1'[inv_amount]),"Net",Sum('Table1'[Net Calc Amount]))
,SUMMARIZE('Table2','Table2'[Customer],'Table1'[created_date],'Table2'[AA Status],"Sales",sum('Table2'[Sales]),"Invoices",count('Table2'[ID]),"Time",DIVIDE(SUM('Table1'[Order Time]),1),"Stage","Order","Gross",sum('Table1'[inv_amount]),"Net",Sum('Table1'[Net Calc Amount]))
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[dobregon]

Thanks a lot @amitchandak for the response,

In reality i dont want to create others columns in my table (imagine a table with millions of rows, if i need to create columns to calculate everthing by column), for that i'm tryigin to have everthing in one measure taking into account only one column

[dobregon]

Something to im thinking now... if it is possible to create a measure that:
1. Create a virtual table with the columns calculated that i have in the variables) by items, date, etc.. So it is necessary that all my filters in the report apply this
2. Create variable measures for each column only refers for each item like the variables in my first message but related to the summarize table.
3. the return will be variable1+varaible2, etc