Forum Discussion

Ackbar-Learner's avatar
Ackbar-Learner
Icon for Resolver I rankResolver I
3 years ago

POWERPIVOT Dax

Hi i need some help on a power pivot dax measure. Hopefully i can send powerpivot questions on this forum as I could not see any specific question related to power pivot.

 

Ok so I am actually building a balance sheet. So I have prepared a table for the Balance Sheet as follows:

Using above table I am doing the balance sheet. I have a whole set of measures working and I have arrived at below stage where I just need to add the totals for Total Assets and Total Equity & Liabilities

 

Last measure used: 

44aBMBS05AllTransactionAmountwithSign:=SUMX(SUMMARIZE (TabBSItem,TabBSItem[BSItem]), CALCULATE( VAR result = [43aBMBS04AsAtAllTransactionAmountwithResultsYTD]*[37BMBSSignMeasure]
        RETURN
            result))

 

Now I need to do two running totals to get the totals for Total Assets and Total Equity & Liabilities and I am using below measures

45aBMBS07AssetsRunningAllTransactionAmountwithSign:=If(
     HASONEFILTER(TabBSItem[BSItem])
     ,
     CALCULATE(
          [44aBMBS05AllTransactionAmountwithSign]
          ,
          ALL(TabBSItem[BSItem])
           ,
          TabBSItem[Index] < 9
          ,
          TabBSItem[Index] < VALUES(TabBSItem[Index])
         )
     ,
     blank()
)
46aBMBS08LiabilitiesRunningAllTransactionAmountwithSign:=VAR Liabilities=
If(
     HASONEFILTER(TabBSItem[BSItem])
     ,
     CALCULATE(
          [44aBMBS05AllTransactionAmountwithSign]
          ,
          ALL(TabBSItem[BSItem])
          ,
          TabBSItem[Index] < VALUES(TabBSItem[Index])
          )
     ,
     blank()
)
RETURN Liabilities - [45aBMBS07AssetsRunningAllTransactionAmountwithSign]

But these two measures are not working as intended and this is where I need some help to understand what is going wrong. Below is the output I am getting.

Grateful to have someone kind assistance and thanks in advance 🙂

 

 

4 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion

    Hi Ackbar-Learner go to your measures definition and check detailst. For assets there is different definition compared with liabilities. Check your mapping also.

     

     

  • some_bih the different definition is because I need to stop the running total asset at total assets and I need to start the running total for liabilities after total assets. And the mapping looks fine to me🧐

    • some_bih's avatar
      some_bih
      Icon for Community Champion rankCommunity Champion

      Hi Ackbar-Learner after Total Assets it is liability, right? If yes, and there is #wrong formula# as in single column it is expected. I would say, check mapping or change measure.

      • Ackbar-Learner's avatar
        Ackbar-Learner
        Icon for Resolver I rankResolver I

        some_bih @Not sure you got my post right. I gave two separate measures for assets and liabilities so that means they are not in the same column, I actually used those formulas before and it gave me the right answers so I am just confused why it is not working now! Let's see if someone else can propose another DAX code