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 there,
Another DAX question from me.
I am trying to show the percentage of potential sales achieved per category.
I would like the actual sales / potential sales based on sales category.
My problem is dat the category is in two different tables (one for actuals and one for potentials). This has me completely confused.
Unfortunately I cannot share my file, so I'll do my best to describe the situation below.
I have one table with sales details, which also includes a details key. In the details table I have the category for the detail key.
There are several rows for each date/detail key combination as I am tracking sales for multiple stores.
In a separate table I have potential sales per product, with a column CategoryName.
So in summary, the data is as follows:
AdditionalDetails
DetailKey | CategoryName |
1 | Fruit |
2 | Vegetable |
3 | Meat |
4 | Vegetable |
5 | Fruit |
Actual Sales
Date | DetailKey | Sales Quantity |
01-01-2020 | 1 | 1 |
01-01-2020 | 2 | 2 |
02-01-2020 | 3 | 4 |
03-01-2020 | 4 | 8 |
03-01-2020 | 5 | 9 |
03-01-2020 | 1 | 3 |
Potential Sales
Date | CategoryName | Potential Sales |
01-01-2020 | Fruit | 154 |
01-01-2020 | Vegetable | 654 |
01-01-2020 | Meat | 16 |
02-01-2020 | Meat | 48 |
02-01-2020 | Vegetable | 56 |
02-01-2020 | Fruit | 758 |
Thanks in advance for your help!!
Solved! Go to Solution.
I did this:
Measure =
VAR __Category = MAX(AdditionalDetails[CategoryName])
VAR __Date = MAX('ActualSales'[Date])
VAR __PotentialSales = LOOKUPVALUE(PotentialSales[Potential Sales],PotentialSales[CategoryName],__Category,PotentialSales[Date],__Date)
RETURN
DIVIDE(SUM(ActualSales[Sales Quantity]),__PotentialSales)
PBIX is attached.
I did this:
Measure =
VAR __Category = MAX(AdditionalDetails[CategoryName])
VAR __Date = MAX('ActualSales'[Date])
VAR __PotentialSales = LOOKUPVALUE(PotentialSales[Potential Sales],PotentialSales[CategoryName],__Category,PotentialSales[Date],__Date)
RETURN
DIVIDE(SUM(ActualSales[Sales Quantity]),__PotentialSales)
PBIX is attached.
Thanks!
Populate the DetailKey from AdditionalDetails
in column Potential Sales
DetailKey = maxx(filter(AdditionalDetails, AdditionalDetails[CategoryName] ='Potential Sales'[CategoryName]),AdditionalDetails[DetailKey])
Now join both table with AdditionalDetails and date dimension and you can use formula
Divide(sum(sales Qty) /sum( potential sales))
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |