Finding corresponding value for the 50% point of a running total

[cdorgan]

I hope someone can help. I have transitioned a data set from Excel to Power BI and can get everything working except finding two numbers. The individual data is categorized by work category where I track the number of issues and cost of issues. To identify my high risk items, I first consolidate the individual items by category with total number of issues and total cost. I then do a running total on the both the cumulative issues and cumulative cost.

Here is a sample of the source data similar to what I have:

Category	Issue Count	Cost
a	1	5000
a	1	2000
a	1	300
b	1	10000
b	1	3000
c	1	500
c	1	0
c	1	2300
c	1	4000
d	1	500
e	1	250
e	1	5000
e	1	6000
e	1	4000
e	1	0
f	1	4500
f	1	2000
g	1	400
g	1	1000
g	1	0

Here is the consolidated data I do my analysis on:

Category	Total Issue Count	Total Cost	RT Issues	RT Cost
a	3	7300	3	7300
b	2	13000	5	20300
c	4	6800	9	27100
d	1	500	10	27600
e	5	15250	15	42850
f	2	6500	17	49350
g	3	1400	20	50750
		50%	10	25375

What I am looking for is where the running total of the issue count is 10 - with the total issue count in ascending order.

I then want to find where the running total of the total cost is 25k - with the toal cost in ascending order.

In Excel I accomplish this as:

Category	Total Issue Count	Total Cost	RT Issues	RT Cost
d	1	500	1	500
b	2	13000	3	13500
f	2	6500	5	20000
a	3	7300	8	27300
g	3	1400	11	28700
c	4	6800	15	35500
e	5	15250	20	50750

For issue count, therefore any category with 3 or more issues is of higher risk.

Category	Total Issue Count	Total Cost	RT Issues	RT Cost
d	1	500	1	500
g	3	1400	4	1900
f	2	6500	6	8400
c	4	6800	10	15200
a	3	7300	13	22500
b	2	13000	15	35500
e	5	15250	20	50750

For cost, it is any category with a cost of 7300 or more.

The end result, only category a and e are high risk for what I have to focus on.

Long questions short - is there anyway in Power BI to find the "3" and "7300" values?

[Anonymous · Accepted solution]

Hi @cdorgan 

I transform your data in Power Query Editor：

Use Group By and Sort the table by Count Total（Ascending）

Result：

Then add an index column:

Now let’s build two calculated columns (Cost RT Issues and RT Cost)

Cost RT Issues = CALCULATE(SUM(Table1[Total Issue]),FILTER(ALL(Table1),Table1[index]<=EARLIER(Table1[index])))

RT Cost = CALCULATE(SUM(Table1[CountTotal]),FILTER(Table1,Table1[Index]<=EARLIER(Table1[Index])))

Result is as below:

Then build measures to find a which is the first Category larger than 10.

50%Count = CALCULATE(SUM(Table1[CountTotal]),ALLSELECTED(Table1))*0.5

50%Issue = CALCULATE(SUM(Table1[Total Issue]),ALLSELECTED(Table1))*0.5

Measure = IF(SUM(Table1[Cost RT Issues])>Table1[50%Issue],MAX(Table1[Category]),BLANK())

Result:

Now we found a and we just need to find the Category whose total Issue >=3 and Count Total>=7300.

Measure 2 = CALCULATE(MAX(Table1[Category]),Table1[Total Issue]>=3,Table1[CountTotal]>=7300)

Result:

If this reply still can't help you to solve your problem, please tell me your calculate logic about how to find a or provide me with more details about your issue just like an issue Screenshot.

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EYmJZHU0S9ZMhYHm...

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.