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.
Hi all,
I'm trying to show a table with some calculations of different sources and I would like to know what's the best approach to achieve that. I will try to sum up as much as possible.
The table I want to get is something like that:
USER_X
Concept | Objective | Weight | Current total | % Achieved | Weight |
Billing Area 10 | 401638.2 | 0.2 | 551022 | 1.37 | 0.2 |
Billing Area 20 | 263342.16 | 0.15 | 187677 | 0.71 | 0 |
Billing Area 30 | 324437.64 | 0.15 | 370785 | 1.14 | 0.15 |
Total Area 10 | 15 | 0.2 | 12 | 0.80 | 0 |
Total Area 20 | 12 | 0.15 | 13 | 1.08 | 0.15 |
Total Area 30 | 10 | 0.15 | 11 | 1.10 | 0.15 |
Total weight | 0.65 |
My current model is:
Objectives_table:
Person_id : key
Year
Billing_10_obj
Billing_20_obj
Billing_30_obj
Billing_10_weight
Billing_20_weight
Billing_30_weight
Total_10_obj
Total_20_obj
Total_30_obj
Total_10_weight
Total_20_weight
Total_30_weight
Bills:
Person_id
Date
Area
Amount
Current total 10 = Sum of all bills amount from that person_id with area = 10
Total 10 = Count all bills from that person_id with area = 10
I will add some examples if the explanations isn't clear enough.
Thanks in advance!
Solved! Go to Solution.
@mdelasheras,
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzIAFEf6n2vq99BP
Regards,
Lydia
@mdelasheras,
An idea is to unpivot columns in your Objectives_table, then merge the Objectives_table and Bills table.
In order for use to provide you detailed steps, please share sample data of your Objectives_table and Bills table, then post expected result based on the sample data.
Regards,
Lydia
Thanks @v-yuezhe-msft! I will add a detailed sample.
Objectives_table:
Person_id | Year | Billing_10_obj | Billing_20_obj | Billing_30_obj | Billing_10_weight | Billing_20_weight | Billing_30_weight | Total_10_obj | Total_20_obj | Total_30_obj | Total_10_weight | Total_20_weight | Total_30_weight |
1 | 2018 | 333300 | 202000 | 121210 | 0.5 | 0.1 | 0.1 | 8 | 6 | 5 | 0.1 | 0.1 | 0.5 |
2 | 2018 | 144000 | 80000 | 50000 | 0.2 | 0.15 | 0.15 | 1 | 2 | 3 | 0.2 | 0.15 | 0.15 |
3 | 2018 | 220000 | 222000 | 222200 | 0.6 | 0.4 | 0.3 | 3 | 4 | 2 | 0.4 | 0.05 | 0.05 |
Bills_table:
Person_id | Date | Area | Amount |
1 | 09/01/2018 | 30 | 684641 |
1 | 18/04/2018 | 30 | 46465 |
2 | 14/03/2017 | 10 | 44100 |
2 | 27/02/2018 | 10 | 89620 |
2 | 18/04/2018 | 20 | 47512 |
2 | 18/04/2018 | 30 | 15687 |
2 | 29/03/2018 | 30 | 51510 |
3 | 22/01/2018 | 10 | 14541 |
3 | 16/04/2018 | 20 | 54981 |
Expected result for person_id = 2 and year = 2018:
Concept | Objective | Weight | Current total | % Achieved (current/obj) | Weight reached* |
Billing Area 10 | 144000 | 0.2 | 89620 | 0.62 | 0 |
Billing Area 20 | 80000 | 0.15 | 47512 | 0.59 | 0 |
Billing Area 30 | 50000 | 0.15 | 67197 | 1.34 | 0.15 |
Total Bills Area 10 | 1 | 0.2 | 1 | 1.00 | 0.2 |
Total Bills Area 20 | 2 | 0.15 | 1 | 0.50 | 0 |
Total Bills Area 30 | 3 | 0.15 | 2 | 0.67 | 0 |
Total weight | 0.35 |
* Weight reached is 0 if % < 1, otherwise is weight
Many thanks!
@mdelasheras,
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzIAFEf6n2vq99BP
Regards,
Lydia
File is currently not available in one drive. Can you please share the same.
Very clear, thank you so much Lydia!
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 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |