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 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
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 |
---|---|
42 | |
21 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |