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

[Anonymous]

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

Vendor	Material Number	Posting Date	Order Quantity	Net Oder Quantity	Final Run Time
HAMI	1101604-4	June 18, 2018	1	28,137.00	33
HAMI	1001504-4	January 22, 2018	1	8,145.00	19
HAMI	1001504-6	January 29, 2018	1	28,137.00
HAMI	1001504-6	January 29, 2018	1	29,544.00	73
HAMI	1001504-6	July 7, 2018	1	29,544.00	51
HAMI	1001504-6	June 22, 2018	1	29,544.00	39
HAMI	1001504-6	November 8, 2017	1	8,145.00	31
HAMI	1001504-6	July 9, 2018	1	29,544.00
HAMI	1001504-6	March 5, 2018	1	28,137.00	23
HAMI	1001504-6	February 20, 2018	1	29,543.85	19
HAMI	1001504-6	January 29, 2018	1	29,543.85	18
HAMI	1001504-6	August 31, 2018	1	29,544.00	34
HAMI	1001504-6	December 1, 2017	1	7,410.00	47
HAMI	1001504-6	April 10, 2018	1	29,544.00	34
HAMI	1001504-6	April 3, 2018	1	29,544.00	50
HAMI	109001504-6	January 3, 2018	1	29,544.00	47
CRB	3059093-01	January 3, 2018	1	0.00
CRB	3056693-01	April 3, 2018	1	0.00
CRB	3076893-01	January 3, 2018	1	0.00
CRB	3056693-01	January 3, 2018	1	0.00
CRB	3056693-01	January 3, 2018	1	0.00
CRB	30909-01	May 3, 2018	1	0.00
CRB	3056693-01	January 3, 2018	1	0.00
CRB	30593-01	January 3, 2018	1	0.00
CRB	3056693-01	January 3, 2018	1	0.00
COMPO	01R3052885-01	January 3, 2018	1	0.00
MEGG	F7XC1A8	January 3, 2018	1	3,802.00	205
COMPO	3030262	January 3, 2018	1	0.00	204
COMPO	30472	May 29, 2018	1	0.00	204
KAV	GM9237-2	November 12, 2017	1	0.00	203
COMPO	3040261	November 21, 2017	1	0.00
Dela	31B5832-04	December 24, 2017	1	1,835.45	202

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.

Vendor	Part Number
HAMI	1001504-4
HAMI	1001504-5
HAMI	1001504-6
HAMI	1002918-10
HAMI	1002918-8
HAMI	1002918-9
HAMI	823800-7
HAMI	1007800-3
HAMI	1007800-4
HAMI	1012632-18
AUTO	31B5237-01
CRB	30B6146-01
RZE	3056915-01
COMPO	3055632CL02
COMPO	3070062-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.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

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:

Regards,

Cherie

[v-cherch-msft · Accepted solution]

Hi @Anonymous

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:

Regards,

Cherie

[Anonymous]

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.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

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)

Regards,

Cherie