Percentage of order type measure / calculation per week

[Anonymous]

Dear all,

I've been googling for quite a while but have not really found a solution yet for a problem I'm trying to resolve. I was hoping somebody could help me out to get a better understanding how to setup specific calculations in PowerBI.

I have a dataset for which I would like to calculate the following things:

1.  Per week the percentage of ordertype count with the following formula:

     Total of ordertype a / (total ordertype b + c + a)

     e.g. wk 1 = (3a)/(b+c+3a) = 60%;

            wk 2 = a / (b+c+a) = 33.3%

2.  Per week the percentage of ordercost with the following formula:

     Total of ordercost a / (total ordercost b + c + a)

     e.g.  wk 1 = 430 / (50+20+10+400+20) = 86%;
             wk 2 = 130 / (140 +154 + 130)  = 31%

Dataset:

Week	Ordertype	Cost
1	a	$        10.00
1	b	$        50.00
1	c	$        20.00
1	a	$      400.00
1	e	$        40.00
1	a	$        20.00
2	a	$      130.00
2	b	$      140.00
2	c	$      154.00
2	e	$      50.00
2	d	$      35.00
2	d	$      62.00
3	a	$      198.57
3	b	$      210.00
3	c	$        50.00
3	c	$      232.86
3	d	$      244.29
3	e	$        60.00
4	f	$      267.14
4	a	$      278.57
4	b	$      290.00
4	b	$      301.43
4	c	$      312.86

[Msta]

If you want to display in one Table both measures (current type/ total of all type from this week) slicing by week/type:

percentage of ordertype =
var __currentType = SELECTEDVALUE(Sheet2[Ordertype])
var __CountOfCurrent = CALCULATE(COUNTROWS(Sheet2), Sheet2[Ordertype] = __currentType)
var __CountOfAll = CALCULATE(COUNTROWS(Sheet2), ALL(Sheet2[Ordertype]))

return

DIVIDE(__CountOfCurrent, __CountOfAll)

SUM of Cost % =
var __currentType = SELECTEDVALUE(Sheet2[Ordertype])
var __CountOfCurrent = CALCULATE(SUM(Sheet2[Cost]), Sheet2[Ordertype] = __currentType)
var __CountOfAll = CALCULATE(SUM(Sheet2[Cost]), ALL(Sheet2[Ordertype]))

return

DIVIDE(__CountOfCurrent, __CountOfAll)

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

divide(calculate(sum(Table[Cost]), filter(Table, table[Ordertype] ="a")), calculate(sum(Table[Cost]), filter(Table, table[Ordertype] in {"a","b","c")))

or

divide(calculate(sum(Table[Cost]), filter(all(Table), table[Ordertype] ="a")), calculate(sum(Table[Cost]), filter(all(Table), table[Ordertype] in {"a","b","c")))

for WOW, use these measure with code WOW in this blog

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Thanks, this was exactly what I was looking for!