• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Eliminate some rows from a list who has at least one "0" value at sub-rows

Hello,

I have a long list of 1,145 recipe numbers (Reçete no below) which explodes into 377,548 rows of pivot table. A few rows are below:

Code:
Reçete no                |Sipariş      |Kalem      | Toplam Stok
-------------------------------------------------------
XXXyyyZZZttt            |1699254       | 10          |  4050
                        |              | 20          |209200
                        |              | 30          | 86000
                        |              | 40          |(empty)
                        |              | 50          |297050
                        |              | 60          |(empty)
                        |              | 70          |297050
                        |              | 80          | 30950
                        |              | 90          |(empty)
                        |1861779       | 10          |  4050
                        |              | 20          | 43750
                        |              | 30          | 16629,2
                        |              | 40          |223697
                        |              | 50          | 37950
                        |              | 60          |  175
                        |              | 70          | 37950
                        |              | 80          |  175
                        |              | 90          |344702
                        |              |100          |  425


What I want is to write a measure which eliminates order number 1699254 (together with all ingredients) form the pivot because it has ingredients which are not in my stock at the moment [(empty) rows].

The final pivot should be:
Code:
Reçete no                |Sipariş      |Kalem      | Toplam Stok
-------------------------------------------------------
XXXyyyZZZttt            |1861779       | 10          |  4050
                        |              | 20          | 43750
                        |              | 30          | 16629,2
                        |              | 40          |223697
                        |              | 50          | 37950
                        |              | 60          |  175
                        |              | 70          | 37950
                        |              | 80          |  175
                        |              | 90          |344702
                        |              |100          |  425

I tried the combinations of CALCULATE, SUMX, SUM, IF but could not avail.

Below is the diagram of my powerpivot model.

powerpivot model.png

Thank you,
 
Last edited:
Hi without a file it's difficult

but I think in calculate where you make sum , you could use as filter expression countrows = count ( I dont know how exactly its function which count numbers in column called. But it would filter for recipe numbers only these where number of rows and number of rows containing number is same......
 
Back
Top