Forum Discussion
DAX: Average per group
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!!
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.
3 Replies
- amitchandak
Super User
Anonymous
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))
- Greg_Deckler
Community Champion
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.
