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.
Hi all, I'm fairly new to Power BI and still trying to grasp the logic behind all the DAX and how to utilize them. Any help is greatly appreciated!
Here is my sample data table:
PO | Item # | Description | Location | Year | Supplier |
100 | AA1 | Apple-Green | STA010 | 2017 | Supplier A |
101 | AA2 | Apple-Red | STA010 | 2018 | Supplier A |
102 | BB1 | Banana-Yellow | STA010 | 2017 | Supplier B |
103 | BB1 | Banana-Yellow | STA020 | 2018 | Supplier A |
104 | BB2 | Banana-Green | STA030 | 2019 | Supplier C |
105 | BB2 | Banana-Green | STA040 | 2017 | Supplier A |
106 | BB3 | Banana-Pink | STA030 | 2019 | Supplier B |
107 | BB3 | Banana-Pink | STA010 | 2017 | Supplier B |
108 | CC1 | Candy-Green | STA010 | 2018 | Supplier A |
109 | CC2 | Candy-Red | STA010 | 2019 | Supplier A |
110 | CC3 | Candy-Yellow | STA020 | 2018 | Supplier C |
111 | CC4 | Candy-Blue | STA030 | 2017 | Supplier B |
112 | DD1 | Doll-Green | STA010 | 2019 | Supplier A |
113 | DD1 | Doll-Green | STA030 | 2018 | Supplier B |
114 | DD2 | Doll-Blue | STA020 | 2017 | Supplier C |
115 | DD2 | Doll-Blue | STA040 | 2018 | Supplier C |
116 | DD3 | Doll-Red | STA010 | 2019 | Supplier A |
117 | DD3 | Doll-Red | STA040 | 2018 | Supplier B |
118 | DD4 | Doll-Pink | STA010 | 2017 | Supplier A |
119 | DD4 | Doll-Pink | STA030 | 2018 | Supplier A |
120 | DD5 | Doll-Yellow | STA030 | 2019 | Supplier C |
121 | DD5 | Doll-Yellow | STA020 | 2017 | Supplier A |
122 | DD5 | Doll-Yellow | STA040 | 2019 | Supplier C |
What I'm trying to achieve: (below is the result I got from Excel by running pivot table counting total PO per year per each item and countif on the side table)
My goal:
- count/% of total item with transaction (PO) > 2 in each year and illustrate on canvas as the sum table (right) below.
2017 | 2018 | 2019 | G. Total | item with transaction > 2 | ||||||
AA1 | 1 | 1 | 2017 | 0 | ||||||
AA2 | 1 | 1 | 2018 | 0 | ||||||
BB1 | 1 | 1 | 2 | 2019 | 1 | |||||
BB2 | 1 | 1 | 2 | |||||||
BB3 | 1 | 1 | 2 | |||||||
CC1 | 1 | 1 | ||||||||
CC2 | 1 | 1 | ||||||||
CC3 | 1 | 1 | ||||||||
CC4 | 1 | 1 | ||||||||
DD1 | 1 | 1 | 2 | |||||||
DD2 | 1 | 1 | 2 | |||||||
DD3 | 1 | 1 | 2 | |||||||
DD4 | 1 | 1 | 2 | |||||||
DD5 | 1 | 2 | 3 | |||||||
Thank you in advance for your help!
New.Bee.P.
Solved! Go to Solution.
Hi p-ha,
According to your description, it seems that you want to get below result, right(if count(po)>1, then show 1 in Year)?
If so, you could use below measure to see whether it work or not
Measure 5 = IF ( CALCULATE ( COUNT ( test[PO] ), FILTER ( test, test[Item #] = MAX ( test[Item #] ) && test[Year] = MAX ( test[Year] ) ) ) > 1, 1, 0 )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This measure works
Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Item #]),Data[Item #],"ABCD",COUNTROWS(Data)),[ABCD]>1))+0
Hope this helps.
Hi,
This measure works
Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Item #]),Data[Item #],"ABCD",COUNTROWS(Data)),[ABCD]>1))+0
Hope this helps.
Hi p-ha,
According to your description, it seems that you want to get below result, right(if count(po)>1, then show 1 in Year)?
If so, you could use below measure to see whether it work or not
Measure 5 = IF ( CALCULATE ( COUNT ( test[PO] ), FILTER ( test, test[Item #] = MAX ( test[Item #] ) && test[Year] = MAX ( test[Year] ) ) ) > 1, 1, 0 )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |