% Calculation is not working

[RanjanThammaiah]

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.

[Icey]

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

[Icey]

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.