Forum Discussion

hussainmw's avatar
hussainmw
New Member
3 years ago
Solved

Calculate formula using All function

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.

  • hussainmw's avatar
    hussainmw
    3 years ago

    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

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      hussainmw
      New Member
      3 years ago

      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