Calculate formula using All function

[hussainmw]

Hi,

I am making a Balance Sheet Power BI on consolidated group level and stand-alone companies.

Currently I have created a measure which pulls the balance amounts of one such company

ALJID = CALCULATE([Total Reporting],'TB Data'[Company]="ALJID")

Now i want to make a measure where i can calculate Total using ALL function (need to lock the Total to use in % formula later). But it is showing ALL total including all Dates (Balance sheet cannot be summation between months).

My date table is in different table, therefore filter ALLEXCEPT function cannot be used.

Please support

Need to make a measure as an extension of ALJID measure, with Summing ALL Assets only in the Balance Sheet except the dates which should be filterable.

[tamerj1]

Hi @hussainmw 

for the denominator you can use

ALJID = CALCULATE([Total Reporting],ALL('TB Data'[Company]))

this will remove the filter only from the Company column and won't affect the date filter.
however it alk depends on the visual's filter context.

[hussainmw · Accepted solution]

I have used the following formula which is working so far. In addition to FILTER and ALL functions I also added MTD function so it does not add two month at any given time (plus use the filters i want to).

ALJID Total Assets = CALCULATE([ALJID], ALL('TB Data'), AccountMapping[Core Category 1]="Assets", DATESMTD(Date_Lookup[Date].[Date]))

Let me know if you have any comments

[tamerj1]

@hussainmw 

As long as it woks then it's good. Good job.