Forum Discussion

Krishnan_47's avatar
Krishnan_47
Icon for Helper I rankHelper I
1 year ago
Solved

To calculate Average and Sparkline in Matrix table

I have two tables:
 
Table 1 as C1 BS1 Parent. It has the following columns and rows:
 
S No
Balance Sheet Items
Parent
6
Other current assets
Current Assets
5
Short term loans and advances
Current Assets
4
Cash and cash equivalents
Current Assets
3
Accounts receivable
Current Assets
2
Inventories
Current Assets
1
Current Investments
Current Assets
10
Short Term Provisions
Current Liabilities
9
Other Current Liabilities
Current Liabilities
8
Accounts Payable
Current Liabilities
7
Short-term borrowings
Current Liabilities
 
 
Table 2 as Base. It has the following columns (sample for screenshot)
 


The data which I have used is present in the excel file, kindly find the dropbox link below:

https://www.dropbox.com/scl/fi/e2bez9uayyt8ethpeapei/Sample-File.xlsx?rlkey=tjev5h5wvq9gcq38uxt2x892y&st=7ufmoajv&dl=0

 
The requirement is to sum all Values under Current Assets and Current Liabilities for each date and get the Current Ratio, the formula for Current Ratio is = Current Assets / (Current Liabilites) * -1.

Expected:

 

My Result:

 

I have loaded the data and have created  a measure to calculate the Current Ratio formula and I am able to get the data as expected for each month wise but when it comes to calculating the average (last column), the calculation is wrong. Kindly help me know where the calculation has gone wrong.

Please find the link below for PBIX file: It is a dropbox link, need to click 'Download' multiple times at a faster pace and the PBIX file will be downloaded.

https://www.dropbox.com/scl/fi/0iqi17m3r9d45bsmzwy6h/Sample-File.pbix?rlkey=kzod73xlck7c5mf93pubgvi2w&st=54uyrar2&dl=0
Need Help
  • BIswajit_Das's avatar
    BIswajit_Das
    1 year ago

    Hello Krishnan_47 
    Try this DAX out

    Ratio_Formula =
    AVERAGEX(
        VALUES('Base'[Attribute]),
        var getCurrentAssets =
        CALCULATE(
        SUMX(
            FILTER(
                'Base',
                LOOKUPVALUE(
                    'C1 BS1 Parent'[Parent],
                   'C1 BS1 Parent'[Balance Sheet Items],
                    'Base'[BS grouping]
                ) IN {"Current Assets"}
            ),
            'Base'[Value]
        )
    )
        var getCurrentLiabilities =
        CALCULATE(
        SUMX(
            FILTER(
                'Base',
                LOOKUPVALUE(
                    'C1 BS1 Parent'[Parent],
                   'C1 BS1 Parent'[Balance Sheet Items],
                    'Base'[BS grouping]
                ) IN {"Current Liabilities"}
            ),
            'Base'[Value]
        )
    ) * -1
        RETURN
            DIVIDE(getCurrentAssets,getCurrentLiabilities)
    )
    i.e

    Thanks & Regards ...

3 Replies

  • BIswajit_Das's avatar
    BIswajit_Das
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    Hello Krishnan_47 
    The Issue in the used DAX is when you are calculating the

    "getCurrentAssets" the "Total" result value is around "710012927"

    and for the "getCurrentLiabilities" the value is around "255872310"

    So according to the DAX the result is

    710012927 / 255872310 which is 2.774

    While the actual result should be 2.83 (the Average)

    Need to modified the DAX which gonna calculate the average while calculating.


    Thanks & Regards...

  • BIswajit_Das's avatar
    BIswajit_Das
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    Hello Krishnan_47 
    Try this DAX out

    Ratio_Formula =
    AVERAGEX(
        VALUES('Base'[Attribute]),
        var getCurrentAssets =
        CALCULATE(
        SUMX(
            FILTER(
                'Base',
                LOOKUPVALUE(
                    'C1 BS1 Parent'[Parent],
                   'C1 BS1 Parent'[Balance Sheet Items],
                    'Base'[BS grouping]
                ) IN {"Current Assets"}
            ),
            'Base'[Value]
        )
    )
        var getCurrentLiabilities =
        CALCULATE(
        SUMX(
            FILTER(
                'Base',
                LOOKUPVALUE(
                    'C1 BS1 Parent'[Parent],
                   'C1 BS1 Parent'[Balance Sheet Items],
                    'Base'[BS grouping]
                ) IN {"Current Liabilities"}
            ),
            'Base'[Value]
        )
    ) * -1
        RETURN
            DIVIDE(getCurrentAssets,getCurrentLiabilities)
    )
    i.e

    Thanks & Regards ...