Forum Discussion

woolf's avatar
woolf
Frequent Visitor
2 years ago

How to GroupyBy to keep filtered values

Hi, I'm fighting with GroupBy/Summarize. I've a imported table with Category, Region, Market, ProductId. I want to filter the table in the dashboard to see the available products in the different shops.

 

As Examples it should deliver the results:

Example 1: Nothing is selected; number of availble products in total -> COUNTA =7
Example 2: Spain is selected; number of availble products in Spain -> COUNTA = 4
Example 3: Spain AND Shop A is selected; number of available products in Shop A -> COUNTA= 2

 

But my formula isn't working. I didn't get the expected result.

GroupBy('ImportedTable','ImportedTable'[Category],'ImportedTable'[Region],'ImportedTable'[Shop],'ImportedTable'[ProductID],"Count",CountAX(CurrentGroup(),'ImportedTable'[ProductID]) 

 

How can i solve it or handle it. Thanx in advance.

 

CategoryRegionShopProductId
PCSpainShop A1574498
PCSpainShop A1574498
PCSpainShop B1574498
PCSpainShop C1574498
PCGermanyShop D1574498
PCItalyShop D1574498
PCNetherlandsShop E1574498
Need Help
Tutorial Requests

3 Replies

    • woolf's avatar
      woolf
      Frequent Visitor
      2 years ago

      Thanx for the quick answer! For showing the numbers it's works perfect. But how can I implement an additional criteria. "Based on the current active filters show me stocks smaller 1 (Order now) and greater 2 (No further action)."

       

      I've two measures now. Count_1 = COUNTA( 'Table'[ProductId] ) and a second one Count_2 = IF([Count_1]=1,"Order now","No further action")

       

      Why I need it based on the filters? If I've 4 pieces in Spain, I can check the different shops and I've to identify is there a Shop with more then 2 pieces and I can shift it from to another. 

       

      That was the reason why I tried it with new table but the filtered values were not available.

      StockCompare=ADDCOLUMNS(GROUPBY('ImportedTable','ImportedTable'[ProductID],"Cnt.",COUNTAX(CURRENTGROUP(),'ImportedTable'[ProductID])),"Stock",IF([Cnt.]=1,"Order now","No further action"))

       

      ProductIdCount_1Count_2
      15744982No further action
      15745361Order now
      15746411Order now
      15746592No further action
      15746691Order now
      15756121Order now
      15756792No further action
      15756931Order now
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi woolf ,

         

        You can do this by simply using an if statement:

        Measure 2 = IF([Measure]=1,"order now","No further action")

         

        Hope it helps!

         

        Best regards,
        Community Support Team_ Scott Chang

         

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