Forum Discussion

binayjethwa's avatar
binayjethwa
Helper V
3 years ago
Solved

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 BL...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

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

    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 column

    Toggle off "Text wrap" option

    Best Regards

lbendlin's avatar
lbendlin
Super User
3 years ago

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

binayjethwa's avatar
binayjethwa
Helper V
3 years ago

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
  • JosephWu's avatar
    JosephWu
    Frequent Visitor
    3 years ago

    Hi , binayjethwa 
    I create a measure like this:


    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