Calculate total rows based on slicer selection

[binayjethwa]

Hi All, 

I have the following table where I have list of  Item.

To calculate weight , i need to count rows of table which is 14 and divide it by 100 as mentioned below.

Also I have a slicer for BLs/subsidiaries , so my count should change bsed on BLs/subsidiaries slicer selection for example for below  BLs/subsidiaries as "One" I have total row count as 14 , if i change slicer to another selection my row count also changes.

Can someone help here.Thanks.

weight =100/14
Total row count =14
Item	Source	BLs/subsidiaries	Weight
Asset	Initiatives	One	7.142857
Overall	BL	One	7.142857
Energy	BL	One	7.142857
Availability	BL	One	7.142857
RE	Initiatives	One	7.142857
Emissions	BL	One	7.142857
Conversion	Initiatives	One	7.142857
Fuel	BL	One	7.142857
Energy	BL	One	7.142857
Reliability	Initiatives	One	7.142857
3E’s	Initiatives	One	7.142857
Reliability	Initiatives	One	7.142857
Thermal	BL	One	7.142857
Reliability	Initiatives	One	7.142857

[v-zhangti]

Hi, @binayjethwa 

You can try the following methods.

Measure = 
Var _N1= CALCULATE(COUNT('Table'[BLs/subsidiaries]),ALLEXCEPT('Table','Table'[BLs/subsidiaries]))
Return
DIVIDE(100,_N1)

Two is four rows.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[binayjethwa]

Hi @v-zhangti  i still see same result. can u please check.

[v-zhangti]

Hi, @binayjethwa 

Based on the screenshots you provided, what do you expect the result to be?

Best Regards,

Community Support Team _Charlotte

[binayjethwa]

@FreemanZ  here count of weight =14 is just given for reference, it will change based on slicer selection and its  dynamic. In above table there are 14 rows hence count is 14 , if there are 4 rows i should see  weight as 25% for each row.

[FreemanZ]

Hi @binayjethwa 

Not sure if i fully get you, then try 

Measure =
VAR _bl =SELECTEDVALUE(TableName[ BLs/subsidiaries])
VAR _count = 
COUNTROWS(
    FILTER(
        ALL(TableName),
        TableName[BLs/subsidiaries] = _bl
    )
)
RETURN
DVIDE(100, _count)

[binayjethwa]

@FreemanZ  The above measure is working fine with single selection , but if i do multiple selections i see weight is calculated sepatately. even if i do multiple selections , for example 2 BL/subsidaries , it should take total count of selected BL/s and divide by 100 as a whole. can you please suggest how we can achieve this.

[FreemanZ]

hi @binayjethwa 

try like:

Measure =

VAR _count = COUNTROWS(TableName)

RETURN DIVIDE(100, _count)

it worked like this:

if it doesn't work, please consider expand your data sample to fully represent the issue and expectation. And continue @me, if furthere suggest is needed. 

[FreemanZ]

Hi   @binayjethwa 

try to write a measure like this:

Measure =

VAR _count = 

COUNTROWS(

    FILTER(

        ALL(TableName),

        TableName[Weight] = 14

    )

)

RETURN

DIVIDE(_count, 100)