Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
binayjethwa
Helper IV
Helper IV

Calculate total rows based on slicer selection

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   
ItemSourceBLs/subsidiariesWeight
Asset InitiativesOne7.142857
Overall BLOne7.142857
Energy BLOne7.142857
Availability BLOne7.142857
RE InitiativesOne7.142857
 EmissionsBLOne7.142857
ConversionInitiativesOne7.142857
Fuel  BLOne7.142857
Energy  BLOne7.142857
Reliability InitiativesOne7.142857
3E’sInitiativesOne7.142857
Reliability InitiativesOne7.142857
Thermal BLOne7.142857
Reliability InitiativesOne7.142857
8 REPLIES 8
v-zhangti
Community Support
Community Support

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.

vzhangti_0-1672116727161.pngvzhangti_1-1672116742607.png

vzhangti_2-1672116806248.png

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.

 

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

binayjethwa_0-1672120955432.png

 

Hi, @binayjethwa 

 

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

 

Best Regards,

Community Support Team _Charlotte

binayjethwa
Helper IV
Helper IV

@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.

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)

@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.

hi @binayjethwa 

try like:

Measure =
VAR _count = COUNTROWS(TableName)
RETURN DIVIDE(100, _count)
 
it worked like this:
FreemanZ_0-1672110595025.pngFreemanZ_1-1672110607336.png

 

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
Super User
Super User

Hi   @binayjethwa 

try to write a measure like this:

Measure =
VAR _count = 
COUNTROWS(
    FILTER(
        ALL(TableName),
        TableName[Weight] = 14
    )
)
RETURN
DIVIDE(_count, 100)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.