Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have an initial table with a column for Accounts, Balance and Date. I need to extract the average per month of the Balances per Account, however I will need to further cross this information with other tables, and therefore I need this information in a new table and not using DAX.
I have already managed to create a column with the average of each Month/Account (“Balance_Avg”) and another one with the respective month (“Date_Month”, I basically transformed all the dates into the first day of the respective month).
I now need a new table where I have only every unique occurrence from “Account” and “Balance_Avg” (otherwise when I use this column as a value it will sum all the ocurrences along with other issues while combining this table).
I had already tried the following approaches:
I have already been stuck in the problem for quite a while, any help would be great.
Thank you in advance for your time.
Original | Original | Original | Calculated | Calculated (w/ DAX) |
Account | Balance | Date | Date_Month | Balance_Avg |
A | 30 | 01/01/2019 | 01/01/2019 | 40 |
A | 60 | 02/01/2019 | 01/01/2019 | 40 |
A | 30 | 03/01/2019 | 01/01/2019 | 40 |
A | 60 | 01/02/2019 | 01/02/2019 | 50 |
A | 30 | 02/02/2019 | 01/02/2019 | 50 |
A | 60 | 03/02/2019 | 01/02/2019 | 50 |
B | 180 | 01/01/2019 | 01/01/2019 | 90 |
B | 60 | 02/01/2019 | 01/01/2019 | 90 |
B | 30 | 03/01/2019 | 01/01/2019 | 90 |
B | 30 | 01/02/2019 | 01/02/2019 | 30 |
B | 30 | 02/02/2019 | 01/02/2019 | 30 |
B | 30 | 03/02/2019 | 01/02/2019 | 30 |
Objective | ||
Account | Date_Month | Balance |
A | 01/01/2019 | 40 |
A | 01/02/2019 | 50 |
B | 01/01/2019 | 90 |
B | 01/02/2019 | 30 |
Solved! Go to Solution.
something like this?
New Table = SUMMARIZE(OriginalTable,OriginalTable[Account],OriginalTable[Date_Month],OriginalTable[Balance_Avg])
something like this?
New Table = SUMMARIZE(OriginalTable,OriginalTable[Account],OriginalTable[Date_Month],OriginalTable[Balance_Avg])
Thank you so much, I was certain that the solution would involve DISTINCT and some sort of LOOKUPVALUE and didn't even assumed to use the simple form of SUMMARIZE.
Once again thank you very much, it worked perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |