Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jørgen Wulff Rasmussen]

?
I need a clever advice on this problem.

I can see that even if my two measures “FAT_QUANTITY” and “Total Vol” ads up correctly, then my last measure takes the total “FAT_QUANTITY” and multiplies with Total “Total Vol”. The result is that the Measure FAT_VOLUME is correct line by line – but not on the total measure – which is the important figure to report.

I have read Matt Allingtons article on the use of sum vs sumx and tried to implement his ideas (see the measure “FAT_VOLUME 2” but with no luck.

FAT_VOLUME = SUMX(FILTER(DIMCALCDBALL;DimCalcDBAll[MatType]=”OFAT”||DimCalcDBAll[MatType]=”FAT”);DimCalcDBAll[Quantity])*SUMX(FactSales;[Total Vol])/1000

FAT_QUANTITY = SUMX(FILTER(DIMCALCDBALL;DimCalcDBAll[MatType]=”OFAT”||DimCalcDBAll[MatType]=”FAT”);DimCalcDBAll[Quantity])

Total Vol = DIVIDE(ROUND(SUMX(FactSales;[Vol]);6);[DivideBy])

Replacement Measure:

FAT_VOLUME 2 = Divide(SUMX(FILTER(DIMCALCDBALL;DimCalcDBAll[MatType]=”OFAT”||DimCalcDBAll[MatType]=”FAT”);DimCalcDBAll[Quantity]);1000)*SUMX(FactSales;[Total Vol])

——————————
Jørgen Wulff Rasmussen
Partner
Exceldoktor
Taastrup
23732009
——————————

[Hasham Niaz]

Hi Jorgen,

Is it possible for you to share the screen shot as well & highlighting the total row which is not summing up correctly.

——————————
Hasham Niaz
Sr. BI Consultant
Karachi, Pakistan
——————————
——————————————-

[Jørgen Wulff Rasmussen]

?Hi Hasham

Please see the attached picture.

As you can see FAT Quantity and Total Volume sums correct. But FAT_Volume does not sum on totals but calculates as 11.96*925.12/1000. That formula is correct when you calculate each productmaterialkey, but when it calculates totals I expected to have the sum of the individual FAT_Volume figures. In this case the sum is 3.65

regards

——————————
Jørgen Wulff Rasmussen
Partner
Exceldoktor
Taastrup
23732009
——————————
——————————————-

[Alison Box]

Hi Jorgen

I think the correct FAT_VOLUME expression should be:-

FAT_VOLUME =

SUMX(

FILTER(

DIMCALCDBALL;DimCalcDBAll[MatType]=”OFAT”

||DimCalcDBAll[MatType]=”FAT”),

DimCalcDBAll[Quantity]*FactSales;[Total Vol])/1000)

 

——————————
Alison Box
Burningsuit Ltd
0800 0199 746
——————————
——————————————-

[Jørgen Wulff Rasmussen]

?Hi Allison,

It seems like the formula does not work at my computer. Did you miss part of the formula? There seems to be more ending paranthesis than opening paranthesis.

also I expect that when you write , it should be ; in my danish version of Power BI?

best regards

Jørgen

——————————
Jørgen Wulff Rasmussen
Partner
Exceldoktor
Taastrup
23732009
——————————
——————————————-

[Alison Box]

Hi Jorgen

Apologies that my expression hasn’t worked for you. I see there was a typo, a semi-colon “FactSales ; [TotalVol]/1000″  which is incorrect.  Also, I wasn’t aware that in Danish you use a semi-colon in place of a comma. 

Here are the measures I used in the screen shot (where my table is called “Products”):-

Incorrect – FAT_VOLUME = SUMX (Products, Products[FAT_QUANTITY]) * SUMX (Products, Products[Total Vol] / 1000)

Correct – FAT_VOLUME2 = SUMX (Products, Products[FAT_QUANTITY] * Products[Total Vol] /1 000)

However, this should be the correct FAT_VOLUME measure (English version) =

SUMX (

FILTER (

DIMCALCDBALL, DimCalcDBAll [MatType]=”OFAT”

||DimCalcDBAll [MatType]=”FAT”),

DimCalcDBAll [Quantity] * FactSales [Total Vol] /1 000)

I’ve also coloured the brackets so you can see where they match (there was an extra one)

The reason your measure didn’t work is that it is the equivalent of :-

FAT_VOLUME3 = SUM (Products [FAT_QUANTITY]) * SUM (Products [Total Vol])/1000

Whereas SUMX iterates all the rows of the table, performs the expression (“Products[FAT_QUANTITY] * Products[Total Vol] /1 000“) for every row and then sums the result for the value in the current filter context (e.g. Product A or Product B).  When it hits the Total row, there is no filter context so the measure just sums the result of this expression for the entire table i.e. all rows.

Hope this helps

Alison

——————————
Alison Box
Burningsuit Ltd
0800 0199 746
——————————
——————————————-

[Jørgen Wulff Rasmussen]

?Hi Alison,

I can follow the formula and your explanation. And I can also make it work, if I work with data in one table. But in this case I have two fact tables.

The first is FACTSALES, which is a table that shows total sales of different products. Here I have to pick up total volume.
The second is DimCalcDBAll which is a table that shows calculations for ingrediens. To calculate the FAT_Volume I have to use the QUANTITY calculated in DimCalcDBAll (which is the sum of quantities for ingrediens in a Product) and the Total Volume sold for that Product.

But when I Combine the two tabels my calculation does not show the same result as you showed.  To compare I have the wrong calculation in PROT_Volume. As you can see I have the result correct on the individual lines – but not in the sum. While if I combine the two tabels as suggested I get a blank column.

Where I did use this formula:

FAT_VOLUME =
SUMX (
    FILTER (
        DIMCALCDBALL;
        DimCalcDBAll[MatType] = “OFAT”
            || DimCalcDBAll[MatType] = “FAT”
    );
    DimCalcDBAll[Quantity] * FactSales[Total Vol]
        / 1000
)

Best regards

Jørgen

——————————
Jørgen Wulff Rasmussen
Partner
Exceldoktor
Taastrup
23732009
——————————
——————————————-