To calculate Average and Sparkline in Matrix table

[Krishnan_47]

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=tjev5h5wvq9gcq38uxt2x892...

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=kzod73xlck7c5mf93pubgvi2...

[BIswajit_Das · Accepted solution]

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 ...

[BIswajit_Das]

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...

[lbendlin]

something like this?