cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamtrangdoan Regular Visitor
Regular Visitor

Visual(s) to signal alerts when Net Order Quantity goes over a threshold or breaks a condition

Hi everyone,

 

What I'm trying to create is a bit complicated and I can't really think of an optimal solution. I hope you can give my some recommendations on where to go with it. Below is the description.

 

I have 2 tables which has a one-to-many relationship and link by "Material Number" column and "Part Number" column.

 

Here is my sample data in the "full updated ALL PARTS" table (These transactions are of 12-month period).

 

VendorMaterial NumberPosting DateOrder QuantityNet Oder QuantityFinal Run Time
HAMI1101604-4June 18, 2018128,137.0033
HAMI1001504-4January 22, 201818,145.0019
HAMI1001504-6January 29, 2018128,137.00 
HAMI1001504-6January 29, 2018129,544.0073
HAMI1001504-6July 7, 2018129,544.0051
HAMI1001504-6June 22, 2018129,544.0039
HAMI1001504-6November 8, 201718,145.0031
HAMI1001504-6July 9, 2018129,544.00 
HAMI1001504-6March 5, 2018128,137.0023
HAMI1001504-6February 20, 2018129,543.8519
HAMI1001504-6January 29, 2018129,543.8518
HAMI1001504-6August 31, 2018129,544.0034
HAMI1001504-6December 1, 201717,410.0047
HAMI1001504-6April 10, 2018129,544.0034
HAMI1001504-6April 3, 2018129,544.0050
HAMI109001504-6January 3, 2018129,544.0047
CRB3059093-01January 3, 201810.00 
CRB3056693-01April 3, 201810.00 
CRB3076893-01January 3, 201810.00 
CRB3056693-01January 3, 201810.00 
CRB3056693-01January 3, 201810.00 
CRB30909-01May 3, 201810.00 
CRB3056693-01January 3, 201810.00 
CRB30593-01January 3, 201810.00 
CRB3056693-01January 3, 201810.00 
COMPO01R3052885-01January 3, 201810.00 
MEGGF7XC1A8January 3, 201813,802.00205
COMPO3030262January 3, 201810.00204
COMPO30472May 29, 201810.00204
KAVGM9237-2November 12, 201710.00203
COMPO3040261November 21, 201710.00 
Dela31B5832-04December 24, 201711,835.45202

 

Here is my "Observed Products" table. All products in the "Part Number" table might or might not appear in the "full updated ALL PARTS" table as we don't always have business with them within a 12-month-period.

 

VendorPart Number
HAMI1001504-4
HAMI1001504-5
HAMI1001504-6
HAMI1002918-10
HAMI1002918-8
HAMI1002918-9
HAMI823800-7
HAMI1007800-3
HAMI1007800-4
HAMI1012632-18
AUTO31B5237-01
CRB30B6146-01
RZE3056915-01
COMPO3055632CL02
COMPO3070062-01

 

I know that the monthly average for all part numbers in the "Observed Products" table is roughly 2 pieces and if order quantity of any of those parts spikes to 4 pieces/month, it's still acceptable and that means the percentage change is 100%.

 

My target is create a visual (or visuals) that can signal alerts whenever:

 

  • Monthly Net Order Quantity of any Part Number in the "Observed Products" table surpasses 4 pieces/month   OR   increases more than 100% when taking into consideration a base number/target volume of 2 pieces/month

 

  • Monthly Average Final Run Time of any Part Number in the "Observed Products" table goes over 28 days    OR   increases more than 30%  when taking into consideration a target TAT of 28 days

Is there a visual that is capable of doing so? If yes, please let me know how you'd do it as I haven't had a single idea how this can work out. Please also let me know if any information is not clear. Thank you so much!

 

PS: @v-cherch-msft's solution below is really awesome and I do appreciate it! However, it'd take me forever to search through and see which part numbers are troubling the business since my file is 60,000 transactions worth of data and viewing it in a data table is not user-friendly at all.

 

Does anyone have any other ideas how to go with my problem in a more visual way? The Average Final Run Time and Net Order Quantity things needn't be in the same visual as long as they show which part numbers are problematic as regards to the conditions written above.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Visual(s) to signal alerts when Net Order Quantity goes over a threshold or breaks a condition

Hi @iamtrangdoan

 

You may create measures as below and use conditional formatting to make the signal alerts. 

Monthly Net Order Quantity =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Order Quantity] ),
    FILTER (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Material Number]
            = MAX ( 'Observed Products'[Part Number] )
    )
)
Monthly Average Final Run Time =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Final Run Time] ),
    FILTER (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Material Number]
            = MAX ( 'Observed Products'[Part Number] )
    )
)
Rule = IF([Monthly Net Order Quantity]>4||[Monthly Average Final Run Time]>28,1)

Then you may try to use conditional formatting for the values in table visual. For example:

1.png

 

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Visual(s) to signal alerts when Net Order Quantity goes over a threshold or breaks a condition

Hi @iamtrangdoan

 

Maybe Multi-row card visual. You may add a filter measure to it and use it in visual level filter to get the special part numbers. For example:

Filter = IF([Monthly Average Final Run Time]>28,1)

1.png

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Visual(s) to signal alerts when Net Order Quantity goes over a threshold or breaks a condition

Hi @iamtrangdoan

 

You may create measures as below and use conditional formatting to make the signal alerts. 

Monthly Net Order Quantity =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Order Quantity] ),
    FILTER (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Material Number]
            = MAX ( 'Observed Products'[Part Number] )
    )
)
Monthly Average Final Run Time =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Final Run Time] ),
    FILTER (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Material Number]
            = MAX ( 'Observed Products'[Part Number] )
    )
)
Rule = IF([Monthly Net Order Quantity]>4||[Monthly Average Final Run Time]>28,1)

Then you may try to use conditional formatting for the values in table visual. For example:

1.png

 

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
iamtrangdoan Regular Visitor
Regular Visitor

Re: Visual(s) to signal alerts when Net Order Quantity goes over a threshold or breaks a condition

Hi @v-cherch-msft,

 

Thank you for your solution, it works really nice, however, I realise it wouldn't be a really good way to search through a 60,000 cells worth of data.

 

Do you happen to have any other ideas on how to go with my problem visually? It needn't be an all-in-one visual for Final Run Time and Net Order Quantity though, they can be in separate visuals as long as they show which part numbers are troubling.

Community Support Team
Community Support Team

Re: Visual(s) to signal alerts when Net Order Quantity goes over a threshold or breaks a condition

Hi @iamtrangdoan

 

Maybe Multi-row card visual. You may add a filter measure to it and use it in visual level filter to get the special part numbers. For example:

Filter = IF([Monthly Average Final Run Time]>28,1)

1.png

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.