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'm getting used to DAX on Power BI.
Below is an example of some of the columns from a query I have imported into Power BI. Notice allowance is different every year, where values are different every quarter
Ideally, this leads me to wanting the table below. Where I am grouping by year, quarter, SUM(VALUES) (for each quarter for that year), allowance
Anyone know how to do this?
Thanks!
Solved! Go to Solution.
Hello,
1) Create measure Sum_Values=Sum([Values])
2) add matrix/table visualisation and add the field how at screenshot
3) After that you need - right click at "Allowance" and click "W/o calculation"
If for some reason you want to create another table (calculated) and not visual...
Add helper column to source table (Table1).
YRQ = [Year] & "-" & [Quarter]
Then in Modeling tab, add New Table
Table2 = DISTINCT(Table1[YRQ])
Build relationship between YRQ column of each table.
Add columns.
For Values:
Values = SUMX(FILTER(Table1,Table1[YRQ]=Table2[YRQ]),[Values])
For Allowance: (Same construct for Year & Quarter, just change the column reference for last argument)
Allowance = MINX(FILTER(Table1,Table1[YRQ]=Table2[YRQ]),Table1[Allowance])
If for some reason you want to create another table (calculated) and not visual...
Add helper column to source table (Table1).
YRQ = [Year] & "-" & [Quarter]
Then in Modeling tab, add New Table
Table2 = DISTINCT(Table1[YRQ])
Build relationship between YRQ column of each table.
Add columns.
For Values:
Values = SUMX(FILTER(Table1,Table1[YRQ]=Table2[YRQ]),[Values])
For Allowance: (Same construct for Year & Quarter, just change the column reference for last argument)
Allowance = MINX(FILTER(Table1,Table1[YRQ]=Table2[YRQ]),Table1[Allowance])
I have just tried this and works brilliantly! Will be very useful for me later
Thanks!
Hello,
1) Create measure Sum_Values=Sum([Values])
2) add matrix/table visualisation and add the field how at screenshot
3) After that you need - right click at "Allowance" and click "W/o calculation"
Simpliest solutions seems to work the best 🙂
Thanks!
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |