cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsonnad
Helper II
Helper II

Get % risk numbers based on Sum of Parent product

@dax @powerx @BIHelp  Need help  i have the following table with parent and child products and would like to derive the risk measures ($amount) in % . as shown in the example . need to create a measure for Column I      E.g. Child product "AA" is a sub product of parent "A" , hence the risk numbers should be a % of the parent total sum which in this case is $10,000.00  for 31st Aug , %risk for row 4 should be H3/SUM(G2:G3) , same for row 2,3 , & 5 . Similarly for Product B category .    Appreciate your help in advance

 

i tried using Calculate(SUM(Total),ALLEXCEPT(Product,Date,ParentProduct)) & assigning the value using SWITH statement but no luck

 

 
 

image.png

3 REPLIES 3
BIHelp
Frequent Visitor

Risk% =
Var Risk =SUM('Table'[Risk])
Var Parent/ChildSale = CALCULATE(
SUM
('Table'[Total]),
ALLEXCEPT('Table', 'Table'[Parent Product], 'Table'[Date], 'Table'[Child Product])
)
Return
DIVIDE( Risk, Parent/ChildSale)
daxer
Solution Sage
Solution Sage

If this is to be a calculated column, then...

[% Risk] = // calculated column
var __parent = T[Parent Product]
var __child = T[Child]
var __date = T[Date]
var __childTotal = T[Total]
var __parentTotal =
	sumx(
		filter(
			T,
			T[Parent Product] = __parent
			&&
			T[Date] = __date
			&&
			T[Child] = __child
		),
		T[Total]
	)
var __risk =
	// If you want to see this number as
	// a percentage, you should use the
	// formatting feature of PBI and not
	// fiddle with this number directly.
	divide(
		__childTotal,
		__parentTotal
	)
return
	__risk
daxer
Solution Sage
Solution Sage

Is this a calculated column or a measure you want?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!