Get value from dimension table and sum with the fact table

[shareezsaleem]

Hi Team,

I have one fact table name as '1. Transactions' and a few dimension table such as 'Calendar','Retained Earnings', 'BS Group'.

I am trying to bring the amount from retained earnings table and sum up with the fact table amount.

I wanted to replace the zero with the amount in the retained earnings table.

FYI, there are 3 amount columns- for UAE, KSA and Overall. So, If I select slicer KSA, I need to pick the KSA amount and use it in above visual.

We have used the below forumula to showcase the numbers in the matrix table.

CALCULATE(SUM('1. Transactions'[BS_Amount]),ALL('Calendar'[Month & Year]),'Calendar'[Date]<=MAX('Calendar'[Date]))+[YTD]

YTD= CALCULATE(TOTALYTD(SUM('1. Transactions'[Amount]),'Calendar'[Date]),'1. Transactions'[Revenue/Expense]<>"BS")

Now I need to add the retained earnings amount formula and use the same in the formula mentioned in the first.

[shareezsaleem]

@amitchandak , seeks your support.

[amitchandak]

@shareezsaleem , based on what I go

if(isblank([measure]), [Retained earning], [Measure])

[shareezsaleem]

Hi Amit,

In the matrix, we have used Month & Year in Column from Calendar Table.

Rows - we have used BS Group Table Level 1

Values- We have used the measures from Transaction Table.

I need to ammend that measure in such a way that - add Amount from retained earnings table to the existing measure. 

I have tried the below measure and sum with the existing measure but doesn't work. Even though the total is showing correct, the summation is not right.

Retained Earnings = if(selectedvalue('1. Transactions'[Company Code Group])="KSA",CALCULATE(SUM('Retained Earnings Table'[Amount KSA]),TREATAS(VALUES('Calendar'[Date]),'Retained Earnings Table'[Dummy Date])),if(selectedvalue('1. Transactions'[Company Code Group])="UAE",CALCULATE(SUM('Retained Earnings Table'[Amount UAE]),TREATAS(VALUES('Calendar'[Date]),'Retained Earnings Table'[Dummy Date])),CALCULATE(SUM('Retained Earnings Table'[Total]),TREATAS(VALUES('Calendar'[Date]),'Retained Earnings Table'[Dummy Date]))))

In short, this is appending 2 tables- One created inside power Bi and one table from Server.

Table from Server has a lot of columns whereas the one created in Power BI (Retained Earning Table) has a few columns- Date, GL Code (292001), Dummy Date, Amount KSA, Amount UAE, Overall Amount

[shareezsaleem]

Hi Amit,

Please see the attached data model screenshot and table.

In the table, you can see subtotal is correct (which we like to see) but when you sum up manually it's not right.

In Value field, we have added 3 measures and named it as Final_Amount:

1. CALCULATE(SUM('1. Transactions'[BS_Amount]),ALL('Calendar'[Month & Year]),'Calendar'[Date]<=MAX('Calendar'[Date]))

2. YTD= 

-1*calculate(TOTALYTD(SUM('1. Transactions'[Amount]),'Calendar'[Date]),'1. Transactions'[Revenue/Expense]<>"BS")

3. Retained Earnings=

-1*(if(selectedvalue('1. Transactions'[Company Code Group])="KSA",CALCULATE(SUM('Retained Earnings Table'[Amount KSA]),TREATAS(VALUES('Calendar'[Date]),'Retained Earnings Table'[Dummy Date])),if(selectedvalue('1. Transactions'[Company Code Group])="UAE",CALCULATE(SUM('Retained Earnings Table'[Amount UAE]),TREATAS(VALUES('Calendar'[Date]),'Retained Earnings Table'[Dummy Date])),CALCULATE(SUM('Retained Earnings Table'[Total]),TREATAS(VALUES('Calendar'[Date]),'Retained Earnings Table'[Dummy Date])))))

[mwegener]

Hi @shareezsaleem ,

how did you merge the 3 measures into the Final_Amount measure?

[shareezsaleem]

Management wanted to see last 6 month's Balance Sheet (Refer the above pic.)

I have all the GL's in a table.

We need to show the accumulated total (start from the company formation) of all GL's except for P&L (Income statement) GL's where we shows only YTD.

We have integrated SAP table with Power BI with all the data (P&L Gl's only from 2021 onwards) are available.

I have made converted Retained Earning Gl to Zero as it has a different calculation.

I made a new retained earnings table in Power BI (model is already shared in previous post).

Coming to your question, I just summed up the measure and somehow it works for Cumulative total and YTD but not for Retained Earnings.

See the below (removed retained earnings)

See the Retained Earnings Table:

I have a slicer in Balance Seet which is 'Region' (UAE, KSA, Overall).

If I select the region UAE, it should pick the amount from 'Amount UAE' column and sum up with the existing Matrix and so on.

Is that possible?

[mwegener]

@shareezsaleem 

I think it is possible.

Only I do not currently see the reason why the correct sum is not formed at the EQUITY and TOTAL level.

Actually, it can only be that a relevant filter does not take effect on these levels.

Can you show only the retained earnings as a measure in the visual?

Where is the "Retained Earning Gl to Zero" calculation?

[shareezsaleem]

Retained Earnings Amount Only:

Converted value to Zero -Retained Earnings:

The reason for creating new table for Retained Earnings (292001) is - we have GL entries only on 12th Month of each year but need to use the same figure across the months of that year plus YTD of last year. I don't know how to create measure for that, so thought of creating a separate table.

You can see one value in November - that is a manual retained earnings comes under this group (GL: 292002).

[mwegener]

Does this matrix now show the values of the measure "Retained Earnings"?

Then I don't understand why these values are present on the aggregation levels values.

[shareezsaleem]

Yeah I noticed that Equity subtotal is not matching with the lines.

Bidirectional is there in the beginning, any problem?

Have you found a solution to fix this issue?

[mwegener]

In older screenshots it was a unidirectional relationship. You should definitely use a unidirectional connection to avoid unwanted effects. This could already be the case here.

I haven't found the problem yet, but can you change the relationship to unidirectional and share a new screenshot of the values of the lines and subtotals?

[shareezsaleem]

Changed as you requested.

Overall-

UAE

KSA

Eventhough the overall Balance sheet is tallying, the equity number of November is not correct.

The retained amount should 2630. While slicing the region, it's not matching at all (Equity).

[mwegener]

Can you add the measures "Cumulative total", "YTD" and "Retained Earnings" as a separate column in the matrix so that we can see which of these measures is generating the variance?

[shareezsaleem]

Overall:

UAE:

KSA:

[mwegener]

Have you changed the relationship to unidirectional?

Why don't you use a relationship between the "Calendar" and the "Retained Earnings Table"?

Currently, I suspect that through the bidirectional relationship only date values are taken into account that also have bookings in the table '1. transactions' and then also per hierarchy level.

[shareezsaleem]

I have tried this but see the below error.

[mwegener]

This is due to the calculated table and BLANK Value. Alberto Ferrari from SQL BI has made a video about this.

https://www.sqlbi.com/tv/avoiding-circular-dependency-errors-in-dax/

Had you then removed the bidirectional relationship?

[shareezsaleem]

I really dont understand.

See the model.

[mwegener]

Hi @shareezsaleem ,

Since when is there a bidirectional filter here?

Also, the sum of Cumulative total and YTD for Equity already does not seem to add up.

See the below (removed retained earnings)