locked
Increase Values of a Table Upto Constant Number by Multiplying a Factor RRS feed

  • Question

  • Hi, 

    I want to increase the sales values in a table upto a number, the sum of all which should be like 100,000. This will be done first by calculating a factor by 100,000/sum(Current Values)=Factor

    Now, Multiplying this (Current Values x Factor Calculated=Answer). 

    What will be DAX formula to create measure that do above calculation on filtered rows based on criteria.

    Please refer to sample file in below

    https://nflpk-my.sharepoint.com/:x:/g/personal/dilawer_hussain_nfoods_com/ETJDzk4au-tPqk4p7s8g2rEBZBOv1PReQ6EJ7TFQqlSRlw?e=P2GEg4

    Friday, February 2, 2018 3:06 AM

Answers

  • I don't like downloading sample files if I can avoid it, but the following pattern of calculations should work:

    Factor:= 100000 / CALCULATE(SUM( table1[Value] ) , ALLSELECTED())
    Answer := SUM(table1[Value])) * [Factor]


    http://darren.gosbell.com - please mark correct answers

    Friday, February 2, 2018 5:24 AM
  • Hi Dilawer,

    Thanks for your response.

    >>>Product A value should increase upto 60,0000
     Product B value should be increased upto 30,000
     Product C value should be increased upto 10,000
    Would you mind sharing the logic to get this data? I can not find it with your sample data.

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, February 5, 2018 6:44 AM

All replies

  • I don't like downloading sample files if I can avoid it, but the following pattern of calculations should work:

    Factor:= 100000 / CALCULATE(SUM( table1[Value] ) , ALLSELECTED())
    Answer := SUM(table1[Value])) * [Factor]


    http://darren.gosbell.com - please mark correct answers

    Friday, February 2, 2018 5:24 AM
  • Hi Dilawer,

    Thanks for your question.

    As I test with your sample file, Darren's solution should work for your scenario.

    ActualValue:=sum(salesvalue[Value])
    
    Factor:=100000 /CALCULATE( [ActualValue],ALLSELECTED(salesvalue))
    
    Answer:=SUM(salesvalue[Value]) * [Factor]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 2, 2018 8:19 AM
  • Thanks Darren & Willson, 

    But my demo file is with criteria 

    First there is filter to be applied in datatable based on product , customer and date. then, 

    Product A value should increase upto 60,0000
    Product B value should be increased upto 30,000
    Product C value should be increased upto 10,000

    First there is filter to be applied in datatable based on product , customer and date

    In any case, Thank you very much for guiding solution. 

    Friday, February 2, 2018 11:20 AM
  • Hi Dilawer,

    Thanks for your response.

    >>>Product A value should increase upto 60,0000
     Product B value should be increased upto 30,000
     Product C value should be increased upto 10,000
    Would you mind sharing the logic to get this data? I can not find it with your sample data.

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, February 5, 2018 6:44 AM