Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Allexcept on multiple dimensions

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?

 

 

celenius_2-1628883225600.png

 

 

Best regards,

 

Chris

 

 

 

 

 

1 ACCEPTED 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] )
)

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

Do you have a separate date dimension table? I'd guess not given this behavior but I would strongly recommend creating one.

Anonymous
Not applicable

@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] )
)

 

Anonymous
Not applicable

@AlexisOlson Hi, Thanks a lot, that did the trick! 

celenius_0-1628908550099.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors