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

Count rows based on slicer

Hi , 

 

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.14285714
Overall BLOne7.14285714
Energy BLOne7.14285714
Availability BLOne7.14285714
RE InitiativesOne7.14285714
 EmissionsBLOne7.14285714
ConversionInitiativesOne7.14285714
Fuel  BLOne7.14285714
Energy  BLOne7.14285714
Reliability InitiativesOne7.14285714
3E’sInitiativesOne7.14285714
Reliability InitiativesOne7.14285714
Thermal BLOne7.14285714
Reliability InitiativesOne7.14285714
 
 
1 ACCEPTED SOLUTION

Hi @binayjethwa ,

Actually, there are 14 rows in the table. And 3 duplicated rows not display in the visual, it is by design. You can open my pbix file(the attachment) for the details...

yingyinr_0-1672206970706.png

If you also want to display these duplicated rows in the visual, you can add one index column in Power Query Editor. Please find the details in the updated attachment.

 Add index columnAdd index column

yingyinr_2-1672207248941.png

Toggle off "Text wrap" optionToggle off "Text wrap" option

Best Regards

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

View solution in original post

8 REPLIES 8
ribisht17
Super User
Super User

Hi @binayjethwa 

 

Here is my video on ALLSELECTED which should clear the concept below and also it will help you to understand the difference between ALL,ALLEXCEPT AND ALLSELECTED

 

https://youtu.be/fEIaJigVfFg

 

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !! PL 300 Certification Series

v-yiruan-msft
Community Support
Community Support

Hi @binayjethwa ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Weight = 
VAR _value = 100
VAR _count =
    CALCULATE ( COUNT ( 'Table'[Item] ), ALLSELECTED ( 'Table' ) )
RETURN
    _value / _count

yingyinr_0-1672194626761.png

Best Regards

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

@v-yiruan-msft  This is working as Expected. Thank you 

Hi @v-yiruan-msft  in the screenshot you shared i see there are 11 rows , hence weight should be 100/11 = 9.09 and not 7.41.

Hi @binayjethwa ,

Actually, there are 14 rows in the table. And 3 duplicated rows not display in the visual, it is by design. You can open my pbix file(the attachment) for the details...

yingyinr_0-1672206970706.png

If you also want to display these duplicated rows in the visual, you can add one index column in Power Query Editor. Please find the details in the updated attachment.

 Add index columnAdd index column

yingyinr_2-1672207248941.png

Toggle off "Text wrap" optionToggle off "Text wrap" option

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

Hi @lbendlin  the requirement is simple , I will have a slicer for BLs where i can multi select the values.

 

Based on my selection i should divide my total count of rows in item column which is 14 in below table and divide by 100 , if i select another  BL which has 5 items i should be dividing 100 by 5 which is 20 and that is what my weight would be.

Not if i multi select in slicer , it should take the overall count and not indivitual count of BL. if i select 2 BLs from dopdown and row count is 10 , then weight would be 100/10. Hope this is clear. THanks in advance.

 

weight =100/14= 7.142857
Total row count in below table =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

Hi , @binayjethwa 
I create a measure like this:

JosephWu_1-1672208224029.png


Because there are many same items name(Reliabilty、Energy), the table only show 11 item not 14.
So,you will see the number is 7.14.
If you want to change the measure,just change the "CountRow()" to "Distinctcount()"

I guess you will get the right answer.

Regards,

Joseph 

 

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.