Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
2 years ago

Counting calculated column for each container

Hi, I am trying to create calculated column that counts number of 'Itemnumbers' per each container.
I created calculated column that gives me nondistinct count of 'Itemnumbers'.
As im example photo container AMFU8923266 has count of 5 'Itemnumbers'.
However at the moment that calculation only exist because of matrix table visual and not as actual column.

 
Let me know if you have any questions

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Justas4478 ,

     

    You can try formula like below:

    M = 
IF(HASONEFILTER('Supplier POL'[Container]),CALCULATE (
    DISTINCTCOUNT ( 'Supplier POL'[Item number] ),
    FILTER (
        ALL ( 'Supplier POL' ),
        'Supplier POL'[Container] = MAX ( 'Supplier POL'[Container] )
    )
),COUNTROWS('Supplier POL'))

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      2 years ago

      Anonymous Hi, I tried your solution and I get wrong results.
      I am getting 778596 when result should be 22 and where number of items in container should be 1 or 2 I am getting 35268 which is total number of items from the data set.

       

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      2 years ago

      Anonymous I fount that I dont have to count the number of itemnumbers.
      Instead I can count number of items container code is repeated.
      I tested and I can get the result I want in excel using this formula: COUNTIF(G:G,[@Container]).
      How ever I dont know how create calculated column in power bi that would give same result.