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 there,
I work for a retail company and I a newbie on POWER BI. I have good knowledge in Excel and I am starting to learn POWERBI DAX. I am trying to do simple things to see If I can start to understand how POWER BI works. I never did a calculated field, at least not one that worked as I wanted it to. So, If someone could point me on the right direction I would be glad.
I want to do a simple report with the number of items sold on the past 10 days and how many of that items where in SALE. I was able to setup the MATRIX to show the date with the past 10 days and the number of items sold per establishment, but I don’t know how to do a count when the PRODUCT is on SALE.
My set of data has all the proper relationships made and it is distributed like this.
My question is how do I create a calculated field that checks if the field DISCOUNT > 0 and count it. I know that I need to add PRODUCT to the matrix so it can bring the discount for each product sold and count them, but I don’t know how to do it.
Thanks in advance!
Regards,
Tiago
Solved! Go to Solution.
In general:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0))
Try putting the table name in front of the second column. You'll definitely need to have all of the tables related to one another. Posting some sample/mock data would be very beneficial to posting a solution that would definitely work. You can also look into RELATED and RELATEDTABLE functions. For example, you could do things like:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table[DISCOUNT] > 0), FILTER(RELATEDTABLE(Table2), [Item_Sold] > 0))
In general:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0))
Hi SMOUPRE,
You are the best. It worked!!! Only thing that is missing is that I need to count just if Item_Sold > 0 how to add that to your expression?
What happened was that sometimes, there is an item that is returned and this item gets stored on database like this Item_Sold = -1. With the measure that you helped me to create if this product that was returned was on SALE it gets counted like a sold item.
Basically I need to add to that measure an IF to the count so it would coult only if Item_Sold >0.
Thanks you for the help!
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0 && [Item_Sold] > 0))
smoupre,
Thank you for the quick response, I will try what you suggested, but DISCOUNT is on the TABLE PRODUCTS_PRICES and Item_Sold is on the TABLE SOLD_ITEMS the way you suggesting DISCOUNT and Item_Sold had to be on the same table no ?
Edit:
As I imagined, it doesnt work, it says that the colum Item_Sold was not found. If you could suggest how to do it would be great.
Thx
Try putting the table name in front of the second column. You'll definitely need to have all of the tables related to one another. Posting some sample/mock data would be very beneficial to posting a solution that would definitely work. You can also look into RELATED and RELATEDTABLE functions. For example, you could do things like:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table[DISCOUNT] > 0), FILTER(RELATEDTABLE(Table2), [Item_Sold] > 0))
Smoupre,
Thank you for the reply, definitely will study DAX, but your help was a great start to understand the language. Apparently you last suggestion worked.
Regards,
Tiago
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |