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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
shanestocks
Regular Visitor

How to show requirements based on value of another column

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

 

shanestocks_1-1710499836756.png

 

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_selectionm_code_selectionstandard_optionalbom_qty
P1000010082633Standard395.71
P1000010082633Standard1163.28
P1000010082633Standard751.67
P1000010082633Standard1167.7
P1000010082633Standard1130.6
P1000010082633Standard977.8
P1000010082633Standard531.74
P1000010082633Standard521.14
P1000010082633Standard522.02
P1000010082633Standard1012.24
P1000010082633Standard897.42
P1000010082633Standard987.51
P1000010082633Standard1056.41
P1000010082633Standard1992.69
P1000010082633Standard1189.78
P1000010082633Standard1135.9
P1000010082633Standard1022.84
P1000010082633Standard1034.33
P1000010082633Standard504.36
P1000010082633Standard469.02
P1000010082633Standard1086.44
P10000RM0023Optional596.22
P10000RM0023Optional751.67
P10000RM0023Optional1203.03
P10000RM0023Optional811.74
P10000RM0023Optional1203.03
P10000RM0023Optional1165.93
P10000RM0023Optional802.02
P10000RM0023Optional1203.03
P10000RM0023Optional781.71
P10000RM0023Optional802.02
P10000RM0023Optional406.31
P10000RM0023Optional811.74
P10000RM0023Optional781.71
P10000RM0023Optional811.74
P10000RM0023Optional401.01
P10000RM0023Optional406.31
P10000RM0023Optional501.71
P10000RM0023Optional1007.83
P10000RM0023Optional511.42
P10000RM0023Optional1002.53
P10000RM0023Optional1002.53
P10000RM0023Optional510.54
P10000RM0023Optional509.65
P10000RM0023Optional1002.53
P10000RM0023Optional1002.53
P10000RM0023Optional1005.18
P10000RM0023Optional511.42
P10000RM0023Optional751.67
P10000RM0023Optional1007.83
P10000RM0023Optional1002.53
P10000RM0023Optional1082.91
P10000RM0023Optional1036.98
P10000RM0023Optional902.72
P10000RM0023Optional1007.83
P10000RM0023Optional511.42
P10000RM0023Optional796.72
P10000RM0023Optional1203.03
P10000RM0023Optional1197.73
P10000RM0023Optional781.71
P10000RM0023Optional1203.03
P10000RM0023Optional787.01
P10000RM0023Optional541.45
P10000RM0023Optional1203.03
P10000RM0023Optional902.72
P10000RM0023Optional1002.53
P10000RM0023Optional1503.35
P10000RM0023Optional984.86
P10000RM0023Optional1002.53
P10000RM0023Optional932.75
P10000RM0023Optional977.8
P10000RM0023Optional1002.53
P10000RM0023Optional1203.03
P10000RM0023Optional1203.03
P10000RM0023Optional1581.96
P10000RM0023Optional781.71
P10000RM0023Optional781.71
P10000RM0023Optional999.88
P10000RM0023Optional1002.53
P10000RM0023Optional498.17
P10000RM0023Optional509.65
P10000RM0023Optional511.42
P10000RM0023Optional511.42
P10000RM0023Optional501.71
P10000RM0023Optional501.71
P10000RM0023Optional501.71
P10000RM0023Optional501.71
P10000RM0023Optional509.65
P10000RM0023Optional1503.35
P10000RM0023Optional998.11
P10000RM0023Optional300.32
P10000RM0023Optional501.71
P10000RM0023Optional1002.53
P10000RM0023Optional995.46
P10000RM0023Optional499.06
P10000RM0023Optional897.42
P10000RM0023Optional1002.53
P10000RM0023Optional1002.53
P10000RM0023Optional1002.53
P10000RM0023Optional998.11
P10000RM0023Optional995.46
P10000RM0023Optional1495.4
P10000RM0023Optional998.11
P10000RM0023Optional501.71
P10000RM0023Optional1000.76
P10000RM0023Optional737.54
P10000RM0023Optional498.17
P10000RM0023Optional501.71
P10000RM0023Optional1002.53
P10000RM0023Optional501.71
P10000RM0023Optional1002.53
P10000RM0023Optional501.71
P10000RM0023Optional1002.53
P10000RM0023Optional997.23
P10000RM0023Optional501.71
P10000RM0023Optional1119.12
P10000RM0023Optional1068.77
P10000RM0023Optional1092.62
P10000RM0023Optional556.47
P10000RM0074Optional1040.51
P10000RM0074Optional1582.84
P10000RM0074Optional547.64
P10000RM0074Optional551.17
P10000RM0074Optional1607.58
P10000RM0074Optional1483.92
P10000RM0074Optional1465.37
P10000RM0074Optional1082.91
P10000RM0074Optional1570.48
P10000RM0074Optional1100.57
P10000RM0074Optional1695.91
P10000RM0074Optional1538.68
P10000RM0074Optional1536.91
P10000RM0074Optional1534.26
P10000RM0074Optional1523.66
P10000RM0074Optional976.91
P10000RM0074Optional1551.93
P1000010082633Standard544.1
P1000010082633Standard1053.76
P1000010082633Standard543.22
P1000010082633Standard1046.69
P1000010082633Standard1062.59
P1000010082633Standard1056.41
P1000010082633Standard1016.66
P1000010082633Standard1036.98
P1000010082633Standard1617.29
P1000010082633Standard1052.87
P1000010082633Standard1635.84
P1000010082633Standard1643.79
P1000010082633Standard1639.37
P1000010082633Standard1492.75
P1000010082633Standard1079.37
P1000010082633Standard443.41
P1000010082633Standard1579.31
P1000010082633Standard937.16
P1000010082633Standard1601.39
P1000010082633Standard1042.27
P1000010082633Standard1065.24
P1000010082633Standard1059.06
P1000010082633Standard1475.08
P1000010082633Standard607.7
P1000010082633Standard1075.84
P1000010082633Standard780.82
P1000010082633Standard1097.04
P1000010082633Standard1045.81
P1000010082633Standard1560.76

 

Thank you for your time.

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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

 

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

 

PMTYPEREQDESIRED
P1000010082633Standard52,443158,549
P10000RM0023Optional83,7140
P10000RM0074Optional22,3910

 

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?

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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.