DAX: Average per group

[mariebel]

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!! 

[Greg_Deckler · Accepted 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.

[mariebel]

Thanks!

[amitchandak]

@mariebel 

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))