Divide value with the different category in the same column

[Anonymous]

Hi all,
I am facing issue while doing the divide: -
Find the sample data blow :-

Date	Device	Duration	Legend
29/05/2022	A	112	Sessions
29/05/2022	B	113	Gap
29/05/2022	C	114	Standalone
30/05/2022	A	111	Sessions
30/05/2022	B	110	Gap
30/05/2022	C	112	Standalone
31/05/2022	A	111	Sessions
31/05/2022	B	113	Gap
31/05/2022	C	111	Standalone

Requirement is :- Session = Count of record where legend is session
                                Gap = count of gap where legend is gap
                                Standalone = To sum up the duration where legend is session divide by number of record where legend is standalone.

Session and gap I could find but the problem is with standalone series.

I wrote a measure to get the output but it showing me a blank in table.

My measure :-
Measure = var _0 = CALCULATE(

    SUM(Table[Duration])/60,FILTER(

        all(table[Legends]),Table[Legends] = "Session"))

var _1 = calculate(COUNT(table[Legends]),

FILTER(table,Table[Legends] = "standalone"))

Return

if(selectedvalue(Table[Legends]) = "Standalone",DIVIDE(_0,_1))

With this formula i get a result in KPI card:-

But on the table i didn't get the value with their legend standalone

[v-luwang-msft · Accepted solution]

HI @Anonymous ,

Change measure to the below:

Measure = var _0 = CALCULATE(

    SUM('Table'[Duration])/60,FILTER(

        all('Table'),'Table'[Legends] = "Sessions"))

 

var _1 = calculate(COUNT('Table'[Legends]),

FILTER('Table','Table'[Legends] = "Standalone"))

 

Return

if(selectedvalue('Table'[Legends]) = "Standalone",DIVIDE(_0,_1))

Output result:(223/60  /3=1.23)

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

Best Regards

Lucien