Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello there,
I have a dataset that looks like this and I am trying to do the following:
Date | ID | SUM of COGS by ID | SUM of Net Sales by ID |
01-Jan-2020 | 201 | 10 | 50 |
01-Feb-2020 | 201 | 10 | 50 |
01-Mar-2020 | 201 | 10 | 50 |
01-Jan-2020 | 202 | 20 | 80 |
01-Feb-2020 | 202 | 20 | 80 |
I would like to create a DAX Measure that calculates blended GGP for each ID no by adopting the following formula: GGP = (Net Sales - COGS)/Net Sales
Basically:
ID 201 = (50-10)/50 =0,8
ID202=(80-20)/80= 0,75
Any suggestions how I can do it?
@krista_003
Add the following measure:
GGP =
DIVIDE(
SUM(Financial[NET SALES]) - SUM(Financial[COGS] ),
SUM(Financial[NET SALES])
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy , Super, thanks! that's exactly what I needed!
By any chance do you know how I can calculate the blended Total GGP% from the table?
ID | GGP | COGS | Net Sales |
201 | 80% | 10 | 50 |
202 | 75% | 20 | 80 |
Total | 77,6% | 30 | 130 |
Total Blended GGP =(130-30)/130=0,766
Usually Power BI calculates total GGP as an average, which is incorrect. How I can change it?
@krista_003
This is how the calculation is done at total level
GGP =(130-30)/130
If you have any other method please explain with expected results and examples.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |