Hey,I have issue in counting ItemVariant for each Item for Brand in selected StoreSo the hierarchie looks like Store/Brand/Item/ItemVariant from top to the bottom I need to count first how many ItemVariants are for Item in selected Store Then I have stock qty measure to see the stock for Item based on ItemVariant in StoreSo when I count ItemVariants in image example ((6) count) for each Item in that Store I want to see percentage At the end I want to devide count(6) with count of stock quantity where quantity = 0 Result I desire is to have extra column where I will get result for Item in percentage Here the result should be - 16.6666667%

Hi pnem , Here are the steps you can follow： 1. Create measure. failedVariant = var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])),"itemgroup",[StockQty]) return IF( [StockQty] = 0 && HASONEVALUE('Table'[item]) ,1, IF( [StockQty] <> 0&& HASONEVALUE('Table'[item]) ,0, IF( 0 in _select && HASONEVALUE('Table'[brand]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])&&[StockQty]=0),[item]), IF( HASONEVALUE('Table'[name]), COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])),[item]), IF( HASONEVALUE('Table'[loc1]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])),[item]),0))))) divide = DIVIDE( [failedVariant],[StockQty]) 2. Result: Best Regards, Liu Yang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Count items in hierarchy | Microsoft Fabric Community

8 Replies

Anonymous

Not applicable

3 years ago

Hi pnem ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure 2 =
IF(
    ISINSCOPE('Flag'[ItemVariant])&&MAX('Flag'[Value])=0,
    FORMAT(
    DIVIDE(
        COUNTX(
            FILTER(ALL(Flag),            'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])&&'Flag'[Value]=0),[ItemVariant]),
        COUNTX(
            FILTER(ALL(Flag),           'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])),[ItemVariant])),"Percent"),
IF(
    ISINSCOPE('Flag'[ItemVariant]),MAX('Flag'[Value]),
IF(
    ISINSCOPE('Flag'[Item]),
    COUNTX(
            FILTER(ALL(Flag),           'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])),[ItemVariant]),
IF(
    ISINSCOPE('Flag'[Brand]),
     COUNTX(
            FILTER(ALL(Flag),
            'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])),[ItemVariant]),
IF(
    ISINSCOPE('Flag'[Store]),
    COUNTX(
            FILTER(ALL(Flag),
            'Flag'[Store]=MAX('Flag'[Store])),[ItemVariant]),
    COUNTX(ALL(Flag),[Value]))))))

2. Result:

Best Regards,

Liu Yang

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

Count items in hierarchy.pbix32 KB

pnem
Frequent Visitor
3 years ago
Hey Anonymous ,

This was very helpful from you but the end result should be slightley modified.
Percentage should be on Item line, not on item variant line.
If you expand hierarchy it could be both ways - item variant rows could be empty or to replacate value from item line

Please advice, thank you!

Anonymous

Not applicable

3 years ago

Hi pnem ,

You can modify it to the following dax:

Flag =
IF(
    ISINSCOPE('Flag'[ItemVariant]),MAX('Flag'[Value]),
IF(
    ISINSCOPE('Flag'[Item]),
    FORMAT(
    DIVIDE(
        COUNTX(
            FILTER(ALL(Flag),            'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])&&'Flag'[Value]=0),[ItemVariant]),
        COUNTX(
            FILTER(ALL(Flag),          'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])),[ItemVariant])),"Percent"),
IF(
    ISINSCOPE('Flag'[Brand]),
     COUNTX(
            FILTER(ALL(Flag),
            'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])),[ItemVariant]),
IF(
    ISINSCOPE('Flag'[Store]),
    COUNTX(
            FILTER(ALL(Flag),
            'Flag'[Store]=MAX('Flag'[Store])),[ItemVariant]),
    COUNTX(ALL(Flag),[Value])))))

Best Regards,

Liu Yang

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

Count items in hierarchy.pbix33 KB

pnem
Frequent Visitor
3 years ago
hey Anonymous

I have created measure which return count for each scope in "hierarchy"

Measure 5 = SWITCH(true(), ISINSCOPE(DimItem[Item Code]),[MeasureITEMVARIANTID], ISINSCOPE(DimItem[Brand]),[MeasureITEMID], ISINSCOPE(DimLocation[Location Name]),[MeasureBRANDID]) MeasureITEMVARIANTID MeasureITEMID MeasureBRANDID are distinccount() for each column

Now I want to count how many 0 are in each scope

Eg. for this item in example i want to have for each 0 null and above number 4 - first level of hierachy
than if we collapse item to brand same thing - just to count 0 for each scope

hope I was clear

Please advice, thanks!

Regards Nem
- pnem
  Frequent Visitor
  3 years ago
  Still facing the issue, any advice?

Anonymous
Not applicable
3 years ago
Hi pnem ,

Sorry, can you present the expected result in the form of a picture, because the data I created with you and I will be different, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- pnem
  Frequent Visitor
  3 years ago
  hey Anonymous
  
  as I already posted -
  
  Measure 5 = SWITCH(true(), ISINSCOPE(DimItem[Item Code]),[MeasureITEMVARIANTID], ISINSCOPE(DimItem[Brand]),[MeasureITEMID], ISINSCOPE(DimLocation[Location Name]),[MeasureBRANDID]) MeasureITEMVARIANTID MeasureITEMID MeasureBRANDID are distinccount() for each column
  
  So this value is retriving number of subcategory for each scope.
  
  I have measure [stock qty] and I want for each category to have some kind of a filter where [stock qty] = 0
  
  want to sum all subcategories where [stock qty] = 0
  
  Eg -
  
  variantQty is measure 5 and I want to create failedVariant measure for each level of "hierachy"
  Then I will calculate further in report like
  
  divide(failedVariant/measure5)
  
  Want to point out that I am using model (SSAS) and I am not able to create calculated columns...
  
  Hope you can understand now better the issue.

Anonymous

Not applicable

3 years ago

Hi pnem ,

Here are the steps you can follow：

1. Create measure.

failedVariant =
var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])),"itemgroup",[StockQty])
return
IF(
    [StockQty] = 0 && HASONEVALUE('Table'[item]) ,1,
    IF(
        [StockQty] <> 0&& HASONEVALUE('Table'[item]) ,0,
    IF(
         0 in _select && HASONEVALUE('Table'[brand]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])&&[StockQty]=0),[item]),
         IF(
             HASONEVALUE('Table'[name]),         COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])),[item]),
             IF(              HASONEVALUE('Table'[loc1]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])),[item]),0)))))
divide =
DIVIDE(
    [failedVariant],[StockQty])

2. Result:

Best Regards,

Liu Yang

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

Count items in hierarchy123.pbix31 KB

Forum Discussion

Count items in hierarchy

8 Replies

Related content

Count counting some items twice

Count all hierarchy nodes including sum of items based on parent

Date hierarchy

Counting nulls

Count of items per day