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

8 Replies

  • 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 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    Best Regards

    • binayjethwa's avatar
      binayjethwa
      Helper V
      3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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