Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
Got this problem I can not figure out, so hopefully someone can assist me with this. Below is a screenshot from a matrix visualisation on a simplified data model that shows my problem.
For each product I would need to get the product's percentage of the total sales within the product group each month.
The Allexcept_Productmeasure shown below gives the result 330 in January for product group A and 10 for product group B. These figures are however the total amounts for all the periods in the data set. I would need the measure to show 180 (A) and 10 (B) in January. There is one-to-many relationship between a Calendar table and the Volume table
I have tried to add another "ALLEXCEPT" for the time dimension, but I can't get this to work. Apparently "ALLEXCEPT" cannot be used on mutiple dimensions, but what would be the best solution to get the results I need?
Best regards,
Chris
Solved! Go to Solution.
OK. In that case, the guide here indicates that you can include it within ALLEXCEPT since it's part of the expanded table.
Allexcep_Productgroup =
CALCULATE (
[Sales],
ALLEXCEPT ( volume, dim_date, volume[productgroup] )
)
Do you have a separate date dimension table? I'd guess not given this behavior but I would strongly recommend creating one.
@AlexisOlson Hi, yes, I have a separate date dimension table and a one-to-many relation between the tables.
OK. In that case, the guide here indicates that you can include it within ALLEXCEPT since it's part of the expanded table.
Allexcep_Productgroup =
CALCULATE (
[Sales],
ALLEXCEPT ( volume, dim_date, volume[productgroup] )
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
18 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
23 | |
22 | |
21 |