Forum Discussion
Calculated DAX column appears twice in Power Pivot modell
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..😥
- I have an opening balance sheet (OB) 2022,
- monthly jornal entries on GL accounts 2022
- closing balance sheet 2022,
- I have one master data with 1 single values for GL accounts and their discription.
- I connected all financial data with a master data and
- 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).
- Monthly jornal entries have D/C classification.
- 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
8 Replies
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.
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')
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.
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
