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 report which generates several versions of the same measure using or ignoring specific slicer selections. I can only get this to work if I construct my slicers from the fact table itself. I could just carry my labels on my fact table but that seems pretty messy. I'm either using the wrong DAX or I have to change the way my fact and dimension tables realate to one another. Below is a look at the statement which does work but only when I focus everything on the fact table. Also there is a simplified picture of the data. Any suggestions would be appreciated. Thanks.
Measure 3 = CALCULATE(AVERAGE(OrderFactTable[Revenue]), ALL(OrderFactTable[employee_id], OrderFactTable[product_id]))
This works but I'd prefer to do something like this below. Can't seem to get the DAX.
Measure 3 = CALCULATE(AVERAGE(OrderFactTable[Revenue]),ALL(Employee[employee_name], Product[product_name]))
Solved! Go to Solution.
Hi @gregv5577,
You can create measures below:
Measure 1 = AVERAGE('OrderFactTable'[revenue])
Measure 2 =
DIVIDE(CALCULATE (
SUM ( OrderFactTable[revenue] ),
ALL ( Employee[employee_name] )
), CALCULATE (
DISTINCTCOUNT(OrderFactTable[product_id]),
ALL ( Employee[employee_name] )
) )
Measure 3 =
DIVIDE(CALCULATE (
SUM ( OrderFactTable[revenue] ),
ALL ( Employee[employee_name] ),ALL('Product'[product_name])),
CALCULATE (
DISTINCTCOUNT(OrderFactTable[company_id]),
ALL ( Employee[employee_name] ),ALL('Product'[product_name])))
You can see attached pbix file.
Best Regards,
Qiuyun Yu
Hi @gregv5577
As an alternative...
Measures:
Total Revenue = SUM(OrderFactTable[revenue])
Measure 1 = [Total Revenue]
Measure 2 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]) )
Measure 3 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]);ALL('Product'[Product_name]))
I hope this helps
Regards
BILASolution
Hi @gregv5577
As an alternative...
Measures:
Total Revenue = SUM(OrderFactTable[revenue])
Measure 1 = [Total Revenue]
Measure 2 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]) )
Measure 3 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]);ALL('Product'[Product_name]))
I hope this helps
Regards
BILASolution
Thanks very much. This is perfect.
Hi @gregv5577,
If our suggestions meet your requirement, would you please mark helpful replies as answers so we can close this thread?
Best Regards,
Qiuyun Yu
Hi @gregv5577,
You can create measures below:
Measure 1 = AVERAGE('OrderFactTable'[revenue])
Measure 2 =
DIVIDE(CALCULATE (
SUM ( OrderFactTable[revenue] ),
ALL ( Employee[employee_name] )
), CALCULATE (
DISTINCTCOUNT(OrderFactTable[product_id]),
ALL ( Employee[employee_name] )
) )
Measure 3 =
DIVIDE(CALCULATE (
SUM ( OrderFactTable[revenue] ),
ALL ( Employee[employee_name] ),ALL('Product'[product_name])),
CALCULATE (
DISTINCTCOUNT(OrderFactTable[company_id]),
ALL ( Employee[employee_name] ),ALL('Product'[product_name])))
You can see attached pbix file.
Best Regards,
Qiuyun Yu
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |