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 daily sales data in a SALES TABLE with two columns: ORDER PURCHASE_DATE and ORDER TOTAL. I also have targets table that has two columns: MONTH and TARGET SALES.
I have to build a measure that aggregates sales by month and compare them to the monthly target.
The output should be: % of TARGET = DIVIDE(MONTHLY SALES, MONTHLY TARGERT).
Thanks,
Erez
Solved! Go to Solution.
But drop a calculated column in the sales table:
Month = MONTH('Sales table'[Order purchase date])
This should allow you to create a one to many relationship from the target table to the sales table (Month to month*)
Then create a measure like this:
TotalOrderSum = Sum('Sales table'[Total Order])
Finally create a table visual and put in:
*Month from taget table
*TotalOrderSum measure
*Target volume from target table
Start by creating a connection between the tables.
I would proberbly use:
Month = MONTH('sales table" [ORDER PURCHASE_DATE])
Then create a relationship to the target sales table.
Finally create a measure,
% of target = ORDER TOTAL / TARGET SALES
Thanks for your answer. I probably was not clear enough in my description.
The TARGET SALES table holds target sales on a month level (so 12 rows).
The SALES TABLE holds sales by day so I have to aggregate sales to month level first ('000s of rows).
But drop a calculated column in the sales table:
Month = MONTH('Sales table'[Order purchase date])
This should allow you to create a one to many relationship from the target table to the sales table (Month to month*)
Then create a measure like this:
TotalOrderSum = Sum('Sales table'[Total Order])
Finally create a table visual and put in:
*Month from taget table
*TotalOrderSum measure
*Target volume from target table
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |