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.
Hello,
I have 2 separate FACTS tables, and do not have a dimension table to link the categories. I tried to manually create the relationship, but it would be many-to-many and would probably slow down the model. How can I create a measure for this?
Facts Sales | |
Product | Sales |
A | 100 |
B | 150 |
C | 110 |
D | 500 |
E | 300 |
Facts Qty sold | |
Product | Sold |
A | 10 |
B | 15 |
C | 20 |
D | 50 |
E | 10 |
I need to have a measure that sums all the product A (assume the tables are only for 1 day) and divide by Product A quantity sold.
the table I am trying to make is
A | B | C | D | E | |
Avg sale | Prod A Sales / Prod B qty |
Solved! Go to Solution.
@OCBB_SFAFPandA Not sure if all the information is there but perhaps something like:
Measure =
VAR __Product = MAX('Facts Sales'[Product])
VAR __Sales = AVERAGE('Facts Sales'[Sales])
VAR __Sold = SUMX(FILTER('Facts Qty sold',[Product] = __Product),[Sold])
RETURN
DIVIDE(__Sales, __Sold,0)
@OCBB_SFAFPandA Not sure if all the information is there but perhaps something like:
Measure =
VAR __Product = MAX('Facts Sales'[Product])
VAR __Sales = AVERAGE('Facts Sales'[Sales])
VAR __Sold = SUMX(FILTER('Facts Qty sold',[Product] = __Product),[Sold])
RETURN
DIVIDE(__Sales, __Sold,0)
Thanks this works @Greg_Deckler ! needed the sum instead of the avg, so just fixed it. Just so I understand, the reason we don't use sumx( or avgx) for the sales category is because it will be redundant if we are already filtering the product by the quantity sold expression?
@OCBB_SFAFPandA Right, so if you are using the Product column from the Fact Sales table in your visual, then the rows in that table are already being filtered down to that Product in the context of the visualization. So, no need to use SUMX/AVERAGEX where you include a FILTER clause like is being done in the Sold calculation.
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 |
---|---|
44 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |