Average based on distinct value in another column

[bvbuijs]

Hi, 

I have a data set that looks like this:

Case	Working hours between	In time? Y=1, N=0
1	32	0
1	4	1
2	3	1
2	3	1
3	2	1
4	1	1
4	7	1
4	21	0
5	4	1

And I need to report a total % of timeliness to a client. 

So I should get these % per case:

Case 1 = (0+1)/2 = 50%

Case 2 = (1+1)/2 = 100%

Case 3 = 100%

Case 4 = (1+1+0)/3 = 66.66%

Case 5 = 100%

So I should report: (50%+100%+100%+66.66%+100%)/5 = 83,332%

What is the best way to do this in PowerBI?

Thanks.

[v-kelly-msft]

Hi @bvbuijs ，

Create 2 measures as below:

_percentage = 
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Case]=MAX('Table'[Case])),'Table'[In time? Y=1, N=0])
var _count=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Case]=MAX('Table'[Case])))
Return
DIVIDE(_sum,_count)

Measure 2 = 
var _table=SUMMARIZE('Table','Table'[Case],"_percentage",[_percentage])
var _sum=SUMX(_table,[_percentage])
var _count=CALCULATE(DISTINCTCOUNT('Table'[Case]),ALL('Table'))
Return
DIVIDE(_sum,_count)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@bvbuijs , Try a new column

New Column = divide(sumx(filter(Table, [Case] = earlier([Case]) ),[ between In time]),countx(filter(Table, [Case] = earlier([Case]) ),[ between In time]))

[parry2k]

@bvbuijs try this measure

DIVIDE (
SUMX ( VALUES ( Table[Case] ), 
DIVIDE ( 
CALCULATE ( SUM ( Table[InTime] ),
CALCULATE ( COUNTROWS ( Table ) )
)
),
COUNTROWS ( VALUES ( Table[Case] ) )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[bvbuijs]

Hi,

@parry2k : I receive an error message when using this formula: "Too few arguments were passed to the DIVIDE function. The minimum argument count for the function is 2".  

My formula looks like this:

Timeliness1_Percentage = DIVIDE (SUMX ( VALUES ('Cases Dump'[CaseNumber]), DIVIDE (CALCULATE ( SUM ('Cases Dump'[Timeliness 1]), CALCULATE (COUNTROWS ('Cases Dump')))), COUNTROWS (VALUES ('Cases Dump'[CaseNumber]))))

How can I solve this?

[v-kelly-msft · Accepted solution]

Hi @bvbuijs ，

You are missing parentheses,modify your measure as below:

Timeliness1_Percentage =
DIVIDE (
    SUMX (
        VALUES ( 'Cases Dump'[CaseNumber] ),
        DIVIDE (
            CALCULATE ( SUM ( 'Cases Dump'[Timeliness 1] ) ),
            CALCULATE ( COUNTROWS ( 'Cases Dump' ) )
        )
    ),
    COUNTROWS ( VALUES ( 'Cases Dump'[CaseNumber] ) )
)

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!