How to show requirements based on value of another column

[shanestocks]

Hi guys,

I have requirements by "P CODE", and each P CODE has choices of "M CODE". In the table, the M CODE is either an optional or standard choice. I want the total requirement by P CODE to be displayed only on the M CODE that is standard.

Current visuals and attempt (sorry for drawing over company):

In the "REQ (KG)" measure its a simple sum of requirement and that is the correct split. In "REQ VISUAL" I have attempted to show only the full requirement, which is 82,069, for the M CODE (meat code) that is the standard, which in this case is the top row. I therefore would like that top row to show what it currently does, 82,069, and the other two as 0. Can this be done?

Current measure:

=CALCULATE(
      [requirement],
           ALL(fUsage[m_code_selection]))

Please see below table of the P10000 code:

p_code_selection	m_code_selection	standard_optional	bom_qty
P10000	10082633	Standard	395.71
P10000	10082633	Standard	1163.28
P10000	10082633	Standard	751.67
P10000	10082633	Standard	1167.7
P10000	10082633	Standard	1130.6
P10000	10082633	Standard	977.8
P10000	10082633	Standard	531.74
P10000	10082633	Standard	521.14
P10000	10082633	Standard	522.02
P10000	10082633	Standard	1012.24
P10000	10082633	Standard	897.42
P10000	10082633	Standard	987.51
P10000	10082633	Standard	1056.41
P10000	10082633	Standard	1992.69
P10000	10082633	Standard	1189.78
P10000	10082633	Standard	1135.9
P10000	10082633	Standard	1022.84
P10000	10082633	Standard	1034.33
P10000	10082633	Standard	504.36
P10000	10082633	Standard	469.02
P10000	10082633	Standard	1086.44
P10000	RM0023	Optional	596.22
P10000	RM0023	Optional	751.67
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	811.74
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	1165.93
P10000	RM0023	Optional	802.02
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	781.71
P10000	RM0023	Optional	802.02
P10000	RM0023	Optional	406.31
P10000	RM0023	Optional	811.74
P10000	RM0023	Optional	781.71
P10000	RM0023	Optional	811.74
P10000	RM0023	Optional	401.01
P10000	RM0023	Optional	406.31
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1007.83
P10000	RM0023	Optional	511.42
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	510.54
P10000	RM0023	Optional	509.65
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1005.18
P10000	RM0023	Optional	511.42
P10000	RM0023	Optional	751.67
P10000	RM0023	Optional	1007.83
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1082.91
P10000	RM0023	Optional	1036.98
P10000	RM0023	Optional	902.72
P10000	RM0023	Optional	1007.83
P10000	RM0023	Optional	511.42
P10000	RM0023	Optional	796.72
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	1197.73
P10000	RM0023	Optional	781.71
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	787.01
P10000	RM0023	Optional	541.45
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	902.72
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1503.35
P10000	RM0023	Optional	984.86
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	932.75
P10000	RM0023	Optional	977.8
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	1203.03
P10000	RM0023	Optional	1581.96
P10000	RM0023	Optional	781.71
P10000	RM0023	Optional	781.71
P10000	RM0023	Optional	999.88
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	498.17
P10000	RM0023	Optional	509.65
P10000	RM0023	Optional	511.42
P10000	RM0023	Optional	511.42
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	509.65
P10000	RM0023	Optional	1503.35
P10000	RM0023	Optional	998.11
P10000	RM0023	Optional	300.32
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	995.46
P10000	RM0023	Optional	499.06
P10000	RM0023	Optional	897.42
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	998.11
P10000	RM0023	Optional	995.46
P10000	RM0023	Optional	1495.4
P10000	RM0023	Optional	998.11
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1000.76
P10000	RM0023	Optional	737.54
P10000	RM0023	Optional	498.17
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1002.53
P10000	RM0023	Optional	997.23
P10000	RM0023	Optional	501.71
P10000	RM0023	Optional	1119.12
P10000	RM0023	Optional	1068.77
P10000	RM0023	Optional	1092.62
P10000	RM0023	Optional	556.47
P10000	RM0074	Optional	1040.51
P10000	RM0074	Optional	1582.84
P10000	RM0074	Optional	547.64
P10000	RM0074	Optional	551.17
P10000	RM0074	Optional	1607.58
P10000	RM0074	Optional	1483.92
P10000	RM0074	Optional	1465.37
P10000	RM0074	Optional	1082.91
P10000	RM0074	Optional	1570.48
P10000	RM0074	Optional	1100.57
P10000	RM0074	Optional	1695.91
P10000	RM0074	Optional	1538.68
P10000	RM0074	Optional	1536.91
P10000	RM0074	Optional	1534.26
P10000	RM0074	Optional	1523.66
P10000	RM0074	Optional	976.91
P10000	RM0074	Optional	1551.93
P10000	10082633	Standard	544.1
P10000	10082633	Standard	1053.76
P10000	10082633	Standard	543.22
P10000	10082633	Standard	1046.69
P10000	10082633	Standard	1062.59
P10000	10082633	Standard	1056.41
P10000	10082633	Standard	1016.66
P10000	10082633	Standard	1036.98
P10000	10082633	Standard	1617.29
P10000	10082633	Standard	1052.87
P10000	10082633	Standard	1635.84
P10000	10082633	Standard	1643.79
P10000	10082633	Standard	1639.37
P10000	10082633	Standard	1492.75
P10000	10082633	Standard	1079.37
P10000	10082633	Standard	443.41
P10000	10082633	Standard	1579.31
P10000	10082633	Standard	937.16
P10000	10082633	Standard	1601.39
P10000	10082633	Standard	1042.27
P10000	10082633	Standard	1065.24
P10000	10082633	Standard	1059.06
P10000	10082633	Standard	1475.08
P10000	10082633	Standard	607.7
P10000	10082633	Standard	1075.84
P10000	10082633	Standard	780.82
P10000	10082633	Standard	1097.04
P10000	10082633	Standard	1045.81
P10000	10082633	Standard	1560.76

Thank you for your time.

[v-heq-msft]

Hi @shanestocks ,

Based on your description, you can use if to conditionally filter the m_code to assign a 0 setting to the ones that are equal to a specific value. Based on the formula you provided, we can't tell how your [REQUIREMENT] is coded. Could you please provide a more detailed explanation and formula on this and please provide your final result in excel form. This way we can help you faster. Please hide your sensitive information in advance.

Best regards,

Albert He

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

[shanestocks]

Hi, please see below desired outcome using Excel (it's really easy to do in Excel formul!). Sorry I do not know how to attach files.

Using the table above as the only data, I would like the following (desired column):

P	M	TYPE	REQ	DESIRED
P10000	10082633	Standard	52,443	158,549
P10000	RM0023	Optional	83,714	0
P10000	RM0074	Optional	22,391	0

The REQ column is simply a sum of the bom qty column, or for the measure, SUM(bom_qty). The matrix is by the P Code, and I would like the total requirement to show only at the M Code which is "standard", and show zero with the ones that are "standard".

Hope this makes sense?