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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.