March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I have a table where the year column represents the claim year, paid years to represent the amount they were paid that year, estimate year is the amount they were estimated to be paid that year and returned year is the amount they paid back that year;
ID | Year | Paid 2019 | Paid 2020 | Paid 2021 | Estimate 2019 | Estimate 2020 | Estimate 2021 | Returned 2019 | Returned 2020 | Returned 2021 |
123 | 2018 | 100 | 150 | 150 | 50 | 50 | 50 | 0 | 50 | 50 |
124 | 2019 | 0 | 150 | 150 | 0 | 200 | 200 | 0 | 100 | 100 |
125 | 2020 | 0 | 0 | 50 | 0 | 0 | 0 | 0 | 0 | 50 |
The total claims amount per year is the Paid + Estimate - Returned.
What I want is to be able to plot the claim per year based on this calculation similar to this;
Year | Paid | Estimate | Recovered | Claim amount |
2019 | 100 | 50 | 0 | 150 |
2020 | 300 | 250 | 150 | 400 |
2021 | 350 | 250 | 200 | 400 |
My first thought was to create a new table with all the paid, estimated and recovered values in which I could then pivot, however, as the actual table ranges around 25 years with each year having 3 columns I thought a SELECTCOLUMNS() statement would be ridiculously large.
Is there another way I can do it or is this the only way?
TYIA
Solved! Go to Solution.
@Data_boi , Unpivot the first table and then split the column with the column name based on space into two column
https://radacad.com/pivot-and-unpivot-with-power-bi
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Split into new columns
Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag
@Data_boi , Unpivot the first table and then split the column with the column name based on space into two column
https://radacad.com/pivot-and-unpivot-with-power-bi
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Split into new columns
Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag
Thank you I'll give this a try!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
133 | |
91 | |
88 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
73 | |
68 |