Forum Discussion

RanjanThammaiah's avatar
RanjanThammaiah
Helper V
7 years ago

% Calculation is not working

Hi All,

 

I have a 2 data sets and which has the relationship with each other.

 

When i try to calculate the formula it works only if i add the column from the dim sheet which i have used for the calculation.

This below is the calculation.

It works fine With the Sales Target-MS with out Sales Target-MS column the formula is wrongly calculating.

Pipeline% FYTDPlan Ach-MS = CALCULATE(DIVIDE(SUM(Sheet1[PipelineFYTD]),SUM('Sales Target-MS'[ Sales Target-MS ])*2))

 

Not sure if there are anything wrong with the calculation. Below is the eg. how it's working.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Icey's avatar
    Icey
    Community Support
    7 years ago

    Hi RanjanThammaiah ,

    If I understand correctly, the relationship between your two tables is ‘Many - to - One’.

    I make a sample to explain your question. If you need, you can download it.

    There is a ‘Many - to - One’ relationship between ‘Table 1’ and ‘Table 2’ based on ‘Product’. The measures are as follows:

    sum of Sales = SUM('Table 1'[Sales])

sum of Stock = SUM('Table 2'[Stock])

Measure = CALCULATE(DIVIDE([sum of Sales],[sum of Stock]))

    The relationship of ‘Stock’ and ‘Sales’ is not ‘One - to - One’. If no ‘Stock’ column in ‘visual 1’, ‘sum of Stock’ doesn’t have any filter context. But in ‘visual 2’ and ‘visual 3’, it has. 

    If you want to show right results in visual 1, you can create a column instead of a measure.

     

    Best Regards,

    Icey Zhang

     

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

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi RanjanThammaiah ,

    Is this problem sloved?
    If it is sloved, could you kindly accept it as a solution to close this case?  
    If not, please let me know.  
     
    Best Regards
    Icey