column with IF order, total incorrect

[Syndicate_Admin]

Hello I have reviewed the posts on similar topics, but no solution suits what I need. I would appreciate assistance with this formula. I have a column (measure) and at row level the result is correct, but the total applies the measure and should be the sum of the rows.

Column contains this measure

Units prov Recibidas 2 = if((SUMx('control prov (2)','control prov (2)'[QTY])-[Total Units PO by Air])>0,[Total Units PO by Air],if(SUMX('control prov (2)','control prov (2)'[QTY])-[Total Units PO by Air]=0,SUMX('control prov (2)','control prov (2)'[QTY]),if((SUMX('control prov (2)','control prov (2)'[QTY])-[Total Units PO by Air])<0,SUMX('control prov (2)','control prov (2)'[QTY]))))

This would be the table where. The total in the column Units prov Received 2 should be 25, and not 43.

Thanks a lot

[Syndicate_Admin]

Thank you very much for your answer.

I have tested what you indicate and created this measure:

Units prov Recibidas 3 = SUMX ( VALUES ( 'control prov (2)'[Reference Comm] ), [Units prov Recibidas 2] )

However, it doesn't quite work for me in my example. When you filter by a value in the main column Reference comm

according to the REf comm data that you select is good, but in others it is not. For example this sum gives 197, but 237 is shown.

Can you think of how to fix it?

If in the total (without filtering any value of the Reference comm column) the result is much closer to the correct 1119, but it still mismatches.

Thanks a lot

sonia

[Anonymous]

Hi @Syndicate_Admin ,

Due to I don't know your table and data model, here I will give you some suggestions.

1. Is [prov po + model] a condtional column like [Reference Comm]? 

If "Yes", I suggest you to use summarize() in SUMX() table field.

2. Does [prov po + model] come from the same table as [Reference Comm]?

If "Yes", meausure should look like as below.

New Units prov Recibidas 2 =
SUMX (
    SUMMARIZE (
        'TableName',
        'TableName'[Reference Comm],
        'TableName'[prov po + model]
    ),
    [Units prov Recibidas 2]
)

If "No", add [prov po + model] based on your data model.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello again. I need help with a similar case. I have several measurements in a table. At the line level it gives me the value well, but not the total of the column.

The table is a matrix where it is broken down by Grouping and Period.

I have tried to apply the solution you gave me in the previous case: sumx(summarize(

However, I can only put one variable in summarize and it doesn't work well if the array contains Grouping and Period.

I would like to be able to add the total of the measure [Max. Sales] by Grouping and by Period, so that the total column is correct.

That is to say something like that but I do not know how to indicate it.

[Max. Sales] is a measure that contains calculations from multiple tables.

Max. Sales 2 = (sumx(SUMMARIZE('Calendar','Calendar'[Period]), and

(SUMMARIZE(Mastermaterialsap,Mastermaterialsap[Grouping],[Max. Sales]))

Thank you very much for your help.

Best regards

sonia

[Syndicate_Admin]

Thank you very much¡¡¡ It has worked perfectly.

[Anonymous]

Hi @Syndicate_Admin ,

Your issue should be caused by IF() function. Here I suggest you to use SUMX() to get sum result based on

[Units prov Recibidas 2] measure.

New Units prov Recibidas 2 =
SUMX ( VALUES ( 'TableName'[Reference Comm] ), [Units prov Recibidas 2] )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.