Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
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, 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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    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.