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.
Basically, I am trying to create something similar to Pivot table in excel and then do calculations using fields in that Pivot table.
Currently I have data which looks like this:
ID | Month | Column 1 | Column2 |
5426 | May | 1 | 0 |
5432 | May | 1 | 0 |
5466 | May | 1 | 0 |
5499 | May | 1 | 0 |
5426 | April | 0 | 1 |
5432 | April | 0 | 1 |
5466 | April | 0 | 1 |
5499 | April | 0 | 1 |
If I were using Excel, I would create a pivot table on this data and create a view like following :
Desired Data | ||
ID | Column 1 | Column2 |
5426 | 1 | 1 |
5432 | 1 | 1 |
5466 | 1 | 1 |
5499 | 1 | 1 |
I just don't know how to achieve this result in Power BI. Once I have this desired result then I want to do more calculations using aggregated values in Column1 and Column2.
Point is I want to do calculations on "aggregated" values of Column1 and Column2 and not the raw data.
Any suggestions will be appreciated.
Thanks.
Solved! Go to Solution.
Hi,
To your Table visual, drag ID to the row labels. Write these measures
Column1 total = SUM(Data[Column 1])
Column2 total = SUM(Data[Column 2])
Hope this helps.
While you “can” load data this way, this is not the best approach. A better approach (generally speaking) is to unpivot the data. Have the column names as attributes and the values in a new column. I have a video showing how here https://youtu.be/K7VS5oqjCxU
once you have this structure, you can drag the columns into a visual in Power BI and build the visuals you need using the aggregates.
Hi,
To your Table visual, drag ID to the row labels. Write these measures
Column1 total = SUM(Data[Column 1])
Column2 total = SUM(Data[Column 2])
Hope this helps.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |