DAX calculated column to retain and blank out certain data + a measure to sum value of that column

[Anonymous]

Hi everyone,

Here is my data:

"full updated ALL PARTS" table:

• Vendor Name
• Material Number (linked) [Note: this column has 60,000 transactions with hundred of product codes that have been repaired during the last 12 months)
• Order Quantity
• Posting Date

"Observed Products" table:

• Part Number (linked) [Note: this column has only 15 product codes to be observed and they might or might not appear in the "Material Number" column of "full updated ALL PARTS" table
• Vendor Name

I'd like to create a calculated column and a measure:

 1.  Calculated column called "Problematic P/N" where if Monthly Total Order Quantity of any products in the "Part Number" column is larger than 10, return the name of that product, otherwise return blank

2.  A measure called "Volume per P/N" to calculate Total Order Quantity of only those products appearing in the newly-created column "Problematic P/N" (ignore blank cells)

The reason I want to have a column and a measure is because I want to use them in a bar chart where the column would use the "Problematic P/N" and the "Volume per P/N" would be counted as Value. 

Please can anyone help me out or have any other ideas? If yes, please show me how you'd do it. Thank you so much!

[v-cherch-msft]

Hi @Anonymous

Attached the sample file. You may change the condition>=3 to >=10 in your real data.

YearMonth = YEAR('full updated ALL PARTS'[Posting Date])*100+MONTH('full updated ALL PARTS'[Posting Date])

Update Number = IF('full updated ALL PARTS'[MonthlyTotalOrderQuantity]>=3,'full updated ALL PARTS'[Material Number])

Problematic P/N =
LOOKUPVALUE (
    'full updated ALL PARTS'[Update Number],
    'full updated ALL PARTS'[Vendor], 'Observed Products'[Vendor],
    'full updated ALL PARTS'[Update Number], 'Observed Products'[Part Number]
)

Volume per P/N =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Order Quantity] ),
    FILTER (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Material Number]
            = MAX ( 'Observed Products'[Problematic P/N] )
            && 'full updated ALL PARTS'[Vendor] = MAX ( 'Observed Products'[Vendor] )
    )
)

Regards,

Cherie 

[Anonymous]

Hi @v-cherch-msft,

1.  I couldn't opened your file because my PBI version is different from yours. But when I tried to create a "Problematic P/N" column, I received this message. Can you please let me know it it means?

2.  What is that "MonthlyTotalOrderQuantity" and how did you come by it?

3.  Do you have any ideas why my "Update Number" column has no values whatsoever, only blanks?

Thank you so much!!!

[v-cherch-msft]

Hi @Anonymous

I would suggest you update the version. Add the calculated column dax as below. Only Volume per P/N is a measure. Please make sure the vendor columns and number columns in two tables are the same types because of the LOOKUPVALUE function.

MonthlyTotalOrderQuantity =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Order Quantity] ),
    ALLEXCEPT (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Vendor],
        'full updated ALL PARTS'[Material Number],
        'full updated ALL PARTS'[YearMonth]
    )
)

full updated ALL PARTS

Observed Products

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft,

Thank you for helping me questions through questions. However, the "Problematic P/N" doesn't seem to work for me even though I know for sure there must be a few products appear under that column. This is what I got:

The "P/N" column contains what are supposed to appear in the "Problematic P/N" and yet I only got blanks for all rows in the "Problematic P/N".

My"P/N" column was created using these measures:

PN = IF ([Volume per P/N] > 10, 'Observed Products'[Part Number], "" )

and this (which I believed you helped me out in my other questions) :

Volume per P/N = 
CALCULATE(
    SUM('full updated ALL PARTS'[Order Quantity]), 
        ALLEXCEPT('Observed Products','Observed Products'[Part Number]) )

I changed my bar chart's elements to these:

and it worked out quite nicely but I spotted some products with volume = 2 or 3 (anything less than 10) still appeared there for some reason, even though I had set the monthly net order quantity to be above 10 previously. And surely that doesn't make sense.

@v-cherch-msft can you please show me how I can fix this? Like, how can I get the graph displayed only those "problematic" products that have Total Order Quantity larger than 10 in only the months they have problems?

Ex:

Net Order Quantity of A in January = 10

Net Order Quantity of A in February= 17

Net Order Quantity of A in March-December = 5

How to only show that problematic product in only January and February and not the rest of the year? (because volume from March-December are less than 10/month)

Also, is it possible to nest a few IFs in my "PN" column above to create multiple thresholds?

Ex: IF Monthly Volume of 823800-7 is larger than 25    AND  IF   Monthly Volume of 1001504-5 is larger than 10   AND IF Monthly Volume of 3070062-01 is larger than 5, RETURN 'Observed Products'[Part Number], OTHERWISE RETURN BLANK.

Is there a way to do all those complicated things?

Sorry for asking so many questions but please know I do appreciate your help!!! Thank you so so much!

[v-cherch-msft]

Hi @Anonymous

Measure Volume per P/N cannot be used in calculated column as they have different context. Please try the calculated column MonthlyTotalOrderQuantity as above and test for your data. You may check the attached file.

For the If condition, column like IF(Part Number="823800-7"&&[MonthlyTotalOrderQuantity]>25,IF(Part Number="3070062-01"&&[MonthlyTotalOrderQuantity]>5,'Observed Products'[Part Number]))). 

If you need further help for your data, It's better if you could share the .pbix file which could reproduce your scenario.You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie