Getting All the values from one table to another u...

Rabi · ‎12-10-2023

Hi Everyone,

Below is then dummy data model, how can i get the budget amount for GAS although there is no transaction in that account in the General Legder using measure.

I have matrix visual, I have used account code and Name in rows from ACCOUNTS table, and below is my measure for getting budget,

Budget = 
VAR Budget = CALCULATE (
    SUM ( budget table[Budget] ),
    Budget table[month]= MAX ( General Ledger[month] ),
    Budget table[Year]= MAX ( General Ledger[Year] ),
    ALL ( Budget table[Account Code])
)

can you please help me to get the result below:

Thanks Verymuch for help !!

parry2k · ‎12-11-2023

@Rabi seems like the dim date table has a relationship with the account table. Why that is there?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k · ‎12-10-2023

@Rabi why you have the following code:

  Budget table[month]= MAX ( General Ledger[month] ),
    Budget table[Year]= MAX ( General Ledger[Year] ),

If there is a date column, then add a date dimension and set the relationship of this date dimension with both GL and Budget table, and the measure should be a simple sum budget.

Also, in the output visual you have shown, you cannot have the same account code row twice until there is another column to make it a unique row.

See

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Rabi · ‎12-11-2023

Hi @parry2k , I tried creating relation from Dim DATE table with Budget table and GL table but it doesnt allow me. It says there will be ambiguty between Account table and DIM date

Rabi · ‎12-10-2023

Hi @parry2k , I have those column in both General ledger and Budget table, I dint wanted to clutter the dummy model that is why i didnt include the date columns, I am using Fiscal Year and Fiscal Period as dates column in both of the tables.

Also my mistake in the output table, there is no duplicate acccount code.