CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
user3812887
user3812887

Reputation: 457

Microsoft Power BI DAX - tricky situation using ALLEXCEPT

I have a situation in which I have a single excel file with one sheet, I am loading this single sheet (Sales) as a Power BI table.

I have two major dimension columns - Model and Product. I have a single numerical column called SalesAmount.

In all there are just 3 source columns in my Sales table.

One Model can have many Products, hence a 1:M relationship, but in the same (Sales) table.

I am using a Power BI matrix, as shown.

enter image description here

I have the following:

1)

Measure called [Sum of SalesAmount]: a simple sum of the SamesAmount column

2)

A Calculated Column called [ModelSalesAmount] in the Sales table:

ModelSalesAmount = CALCULATE(
                             SUM(Sales[SalesAmount]),
                             ALLEXCEPT(Sales,Sales[Model])
                            )

A Calculated Column called [ProductOnModel] in the Sales table:

ProductOnModel = Sales[SalesAmount]/Sales[ModelSalesAmount]

I have the matrix visual as shown.

The aggregated value for the ModelSalesAmount is incorrect.

HL Fountain Frame is a Model, and there are 8 products under this Model.

The Calculated column Sales[ModelSalesAmount] with a SUM aggregation in the matrix, works correctly at the product level (3,365,069.274), but not at the Model level. The Model level aggregation 26,920,554.19 is incorrect. I would want 3,365,069.274 at the Model level, NOT 26,920,554.19.

Interestingly, the Calculated Column Sales[ProductOnModel] with a SUM aggregation in the matrix, while using the Sales[ModelSalesAmount] Calculated Column in the denominator works correctly, both at the individual product level, as well as at the Model level!

What should I do to remove the undesired value 26,920,554.19, and make it 3,365,069.274 ?

I tried this below, but not working:

ModelSalesAmount_CC =  IF (

                            HASONEVALUE(Sales[Product]) = TRUE,
                            
                            CALCULATE(

                                      SUM(Sales[SalesAmount]),

                                      ALLEXCEPT(Sales,Sales[Model])

                                     ),

                             ...........

                           )

Any suggestion please?

Upvotes: 0

Views: 821

Answers (1)

user3812887
user3812887

Reputation: 457

Change the Calculated Column [ModelSalesAmount] to a measure, I get the right output. Thanks Alexis.

Upvotes: 0

Related Questions