Calculated DAX column appears twice in Power Pivot modell

[Anonymous]

Hello to everyone, new in the community, but happy to find it out and hopefully you can help me🙂.

Now I have a headache with one date modell for accruals. Unfortunatelly there still so few people who are aware about DAX etc. I have nobody to ask..😥 

1. I have an opening balance sheet (OB) 2022,
2. monthly jornal entries on GL accounts 2022 
3. closing balance sheet 2022,
4. I have one master data with 1 single values for GL accounts and their discription.
5. I connected all financial  data with a master data and
6. created DAX calculate measure (with filter on master GL data, year and month)  for OB  in order to connect it with   monthly jornal entries data (otherwise n:M doesn't work).
7. Monthly jornal entries have D/C classification.
8. My problem is , when I put OB in PowerPivot Modell+ Month+ D/C, my DAX column with OB is always repeating every month due to addition D/C, I want only to have in January (only once).  I would so much appriciate, if you can give just an idea, how I can solve that.  Many,many Thanks! KR Anjela

[jennratten]

Hello - it sounds like the cause of your problem is the location of the fields/measures in the pivot table.  OB Balance is repeating for each month because there is another field between it an month in the pivot table.  You will need to rearrange your fields/measures to get the outcome you are looking for.  Most likely, you will need to drag month down one level so that it is just above OB Balance.

[Anonymous]

Hello, many,many thanks for your tipp!unfortunatelly I tried already nearly all variances without any sucess, in addition to that am not able to create html to load all data to forum. I think I have to change something in DAX, but after many tries, also still no sucess😭. I know, I should not post screeshorts, but I don't see any way now. 

OB 1:=CALCULATE(sum(SAP_F_01_OB[Summe der Berichtsperiode]);'Calendar'[Year]=2022;'Calendar'[Month]="Januar";'Master GL')

[jennratten]

Make sure the fields in your pivot table (not the measures) are coming only from the dimension tables (Master GL and Calendar).  I've marked the fields that I could match to the tables in RED.  The fields in BLUE also need to only come from the dimension tables but I can't determine which is correct based on their names and the screenshot.   Then you can revise your measure script to the following:

OB 1:=sum(SAP_F_01_OB[Summe der Berichtsperiode])

Please let me know the outcome in case further modifications are needed. 

[Anonymous]

after thinking: SUM of OB:  Filter is here only Year/Month, SAP_FBL3N: Filter is here C/D, Year/Month. How can we exclude OB from C/D filter? in order to get only OB separat column first following by filtered values from FBL3N based on C/D?

[Anonymous]

Good Morning🙂, all GLs/month/year are coming from dimension tables, only D/C are from SAP_FBLN3N. I think that may be a problem, you are right. I guess I have to think how to get D/C into Master_GL table. Thank you for a tip! I will let you know, if I could solve that. 1000 Thanks!

[Anonymous]

Hello, I am still here.. that doesn't work. I created separat short dimtable only for C/D and connected that with SAP_FBL3N Table, but OB is still repeating all the time in C/D column... Maybe DAX will be still a solution with filter excluding C/D, as OB is only a sum in the table without C/D....

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Hello, I am not able to create html from powerpivot..(( struggeling since hours, is here any other opportunity to load the data model? 1000 thanks! KR Anjela