Dax Equivalent of SQL Partition by Combined with SumProduct

[PowerBIUser349]

Hello Everyone, 

I'd greatly appreciate some help. Below you will find an example of what I am trying to do. The use case in this example doesn't really make sense, but it is identical to the problem I am trying to solve. I know how to solve this question in SQL, but I don't know the dax. I am using a partition to find the weighted average of a SumProduct. Here is the script to give you an idea of what I am trying to do... the fourm doesn't have SQL source code so I chose HTML... Thank you! 

If OBJECT_ID ('Products') is not null Drop Table Products

Create Table Products
	(ProductName nvarchar(30)
	,Color nvarchar(30)
	,Units decimal(8,2)
	,Revenue decimal(8,2))
Go 

INSERT INTO Products VALUES 
		('Bike','Red',5,20)
		,('Bike','Blue',1,3)
		,('Helmet' ,'Red',8,32)
		,('Helmet','Blue',1,3)
		,('Hat','Red',2,8)
		,('Glasses','Blue',1,4)
		

;With CTE as (
Select 
	ProductName
	,Sum(Units) as SumofUnits
	,Sum(Revenue) as SumofRev
	,Sum(Units) * (Sum(Revenue) Over(Partition by Color)/Sum(Units) Over(Partition by Color)) as Weighted
From Products p
Group by 
	ProductName
	,Color
	,Units
	,Revenue)

Select 
	ProductName
	,Sum(SumofUnits) as Units
	,Sum(SumofRev) as Revenue
	,Sum(SumofRev)/Sum(SumofUnits) as ProductRevPerUnit
	,Sum(Weighted)/Sum(SumofUnits) as ColorRevPerUnit
From CTE
Group by ProductName

[lbendlin]

It is generally futile to chase the "What is the equivalent in system B to what I am used to in system A" quest.  It has a name - "Fighting the API".  Instead you look at your business problem and find a good way to answer that in system B.

Your weighted average calculation seems to go back and forth on the number of units.  There might be an easier calculation.  Can you describe the business request?

See attached for a sample implementation (without the ColorRevPerUnit)

[PowerBIUser349]

Hi Lbedlin, I appreaciate your response and help. I don't necessarily want my dax logic to be the same as the SQL script, but I do need the same results. Think of the ColorRevPerUnit as a benchmark. I need to create a ratio (per unit) and compare that ratio (benchmark) back to each value in the cohort. The reason for the weighted average is because each product can be in multiple color cohorts, therefore I need to create a weighted per average value. Again, not married to the SQL logic... just need the same results.  

[lbendlin]

I think what you want is 

Weighted = CALCULATE(DIVIDE(sumx('Table',[Units]*[Revenue]),sumx('Table',[Units]),0),REMOVEFILTERS('Table'[ProductName]))

or - more brutal - 

Weighted = CALCULATE(DIVIDE(sumx('Table',[Units]*[Revenue]),sumx('Table',[Units]),0),ALLEXCEPT('Table','Table'[Color]))

[PowerBIUser349]

Hi lbendlin, thank you again for your help. I see what you're dax is doing, unfortunately it does not provide the answer I am looking for.  Below is a table with the dax you provided:

Here is the results I am looking for:

These results allow me to compare the two ratios. 

[lbendlin]

I don't see that result when I run the SQL query

with [Products] as (
select 'Bike' [ProductName],'Red' [Color],5 [Units],20 [Revenue]
union all select 'Bike','Blue',1,3
union all select 'Helmet' ,'Red',8,32
union all select 'Helmet','Blue',1,3
union all select 'Hat','Red',2,8
union all select 'Glasses','Blue',1,4
)
,CTE as (
Select 
	ProductName
	,Sum(Units) as SumofUnits
	,Sum(Revenue) as SumofRev
	,Sum(Units) * cast(Sum(Revenue) Over(Partition by Color)/Sum(Units) Over(Partition by Color) as float) as Weighted
From Products p
Group by 
	ProductName
	,Color
	,Units
	,Revenue
	
	
	)

Select 
	ProductName
	,Sum(SumofUnits) as Units
	,Sum(SumofRev) as Revenue
	,cast(Sum(SumofRev) as float)/Sum(SumofUnits) as ProductRevPerUnit
	,cast(Sum(Weighted) as float)/Sum(SumofUnits) as ColorRevPerUnit
From CTE
Group by ProductName

[PowerBIUser349]

This is because you're not creating a table with defined data types. You're script is inserting the values into a CTE which is then letting you DBMS decide the data type. Create a table with defined data types and it will return the correct results. Use this script: 

If OBJECT_ID ('Products') is not null Drop Table Products

Create Table Products
	(ProductName nvarchar(30)
	,Color nvarchar(30)
	,Units decimal(8,2)
	,Revenue decimal(8,2))
Go 

INSERT INTO Products VALUES 
		('Bike','Red',5,20)
		,('Bike','Blue',1,3)
		,('Helmet' ,'Red',8,32)
		,('Helmet','Blue',1,3)
		,('Hat','Red',2,8)
		,('Glasses','Blue',1,4)

;With CTE as (
Select 
	ProductName
	,Sum(Units) as SumofUnits
	,Sum(Revenue) as SumofRev
	,Sum(Units) * Sum(Revenue) Over(Partition by Color)/Sum(Units) Over(Partition by Color) as Weighted
From Products p
Group by 
	ProductName
	,Color
	,Units
	,Revenue
	)

Select 
	ProductName
	,Sum(SumofUnits) as Units
	,Sum(SumofRev) as Revenue
	,Sum(SumofRev)/Sum(SumofUnits) as ProductRevPerUnit
	,Sum(Weighted)/Sum(SumofUnits) as ColorRevPerUnit
From CTE
Group by ProductName

[lbendlin · Accepted solution]

I see. I corrected my SQL.

with [Products] as (
select 'Bike' [ProductName],'Red' [Color],5 [Units],20.0 [Revenue]
union all select 'Bike','Blue',1,3.0
union all select 'Helmet' ,'Red',8,32.0
union all select 'Helmet','Blue',1,3.0
union all select 'Hat','Red',2,8.0
union all select 'Glasses','Blue',1,4.0
)
,CTE as (
Select 
	ProductName
	,Sum(Units) as SumofUnits
	,Sum(Revenue) as SumofRev
	,Sum(Units) * (Sum(Revenue) Over(Partition by Color)/Sum(Units) Over(Partition by Color) ) as Weighted
From Products p
Group by 
	ProductName
	,Color
	,Units
	,Revenue
)
Select 
	ProductName
	,Sum(SumofUnits) as Units
	,Sum(SumofRev) as Revenue
	,Sum(SumofRev) /Sum(SumofUnits) as ProductRevPerUnit
	,Sum(Weighted) /Sum(SumofUnits) as ColorRevPerUnit
From CTE
Group by ProductName

Now I get the same as you.  So the measure would be

Weighted = 
var a = ADDCOLUMNS(Products,"w",[Units]*CALCULATE(divide(sum(Products[Revenue]),sum(Products[Units]),0),ALLEXCEPT(Products,Products[Color])))
return sumx(a,[w])

[PowerBIUser349]

Awesome. Thank you!

[lbendlin]

updated previous post

[PowerBIUser349]

You my friend are a life saver! Thank you! I very much appreciate you taking time out of your day(s) to help me. 

[lbendlin]

thank you for your patience.  I really need to brush up on my SQL knowledge.