Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a list of over 5,000 Purchase order numbers. Each Purchase order can have a variety of different account codes attached to it. There are 14 account codes. I would like to Identify Purchase orders that do not have account code (10C) attached to it. For Example in the table below Purchase order 2,4,6,8 would qualify as a purchase order that does not have account code 10C. Purchase order 1 would not qualify because although it has other account codes it also has 10C. How can I create a measure that allows me to only view purchase orders without account code 10C?
Purchase Order No | Acct Cd | Sum of Amt |
1 | 10C | 9624.26 |
1 | 20B | 14209.52 |
1 | 20C | 9001.25 |
2 | 20D | 3624.93 |
2 | 20B | 26953.98 |
3 | 10C | 1750.5 |
4 | 20E | 1425.67 |
4 | 20D | 35491.18 |
4 | 20A | 2450.15 |
5 | 10C | 12901.04 |
6 | 20A | 4593.94 |
6 | 20B | 833.9 |
7 | 10C | 200 |
7 | 20A | 486.39 |
7 | 20B | 100 |
8 | 20A | 156 |
8 | 20C | 122 |
Solved! Go to Solution.
Hi @dw700d
Try this measure :
Purchase <>(10C) =
VAR _Count10C =
CALCULATE (
COUNT ( 'Table'[Sum of Amt] ),
ALLEXCEPT ( 'Table', 'Table'[Purchase Order No] ),
'Table'[Acct Cd] = "10C"
)
RETURN
IF ( ISBLANK ( _Count10C ), 0, _Count10C )
and filter the new meausre to show zero.
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi @dw700d
Try this measure :
Purchase <>(10C) =
VAR _Count10C =
CALCULATE (
COUNT ( 'Table'[Sum of Amt] ),
ALLEXCEPT ( 'Table', 'Table'[Purchase Order No] ),
'Table'[Acct Cd] = "10C"
)
RETURN
IF ( ISBLANK ( _Count10C ), 0, _Count10C )
and filter the new meausre to show zero.
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
@dw700d , Plot this as total or with purcahse order no
Measure =
var _1 = calculate(sum(Table[Amt]), filter(Table, Table[Acc Cd] ="10C"))
return
countx(values(Table[Purchase Order No]), if(isblank(_1) ,[Purchase Order No], blank()))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |