Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pnem
Frequent Visitor

Count items in hierarchy

Hey,

I have issue in counting ItemVariant for each Item for Brand in selected Store
So 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 Store
So 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 Screenshot .png


Result I desire is to have extra column where I will get result for Item in percentage 
Here the result should be - 16.6666667% 


1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1692869676244.png

 

 

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

View solution in original post

8 REPLIES 8
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1692869676244.png

 

 

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

v-yangliu-msft
Community Support
Community Support

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

hey @v-yangliu-msft 

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 - 

 Screenshot .png

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.

v-yangliu-msft
Community Support
Community Support

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

vyangliumsft_1-1691398829121.png

 

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.

hey @v-yangliu-msft 

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 


Screenshot .png


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

Still facing the issue, any advice? 

v-yangliu-msft
Community Support
Community Support

Hi  @pnem ,

I created some data:

vyangliumsft_0-1691375728892.png

 

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:

vyangliumsft_1-1691375728894.png

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

Hey @v-yangliu-msft ,

 

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 

Screenshot .png

 

 

Please advice, thank you!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors