Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

column with IF order, total incorrect

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...
Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    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