Forum Discussion

slhyrkl's avatar
slhyrkl
Frequent Visitor
2 years ago
Solved

How to show variation for multiple data points?

I have a dataset which has multiple values for different dates for specific part numbers. These values are getting calculated monthly but that's a new process so most of the part numbers don't have a...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi slhyrkl ,

    First of all, many thanks to ryan_mayu  for your very quick and effective replies.

    Based on my testing, please try the following methods:

    1.Create the simple table.

    2.Create the new measure to calculate the monthly values.

    Monthly values = CALCULATE(SUM('Table'[Values]), FILTER('Table', 'Table'[Month]))

    3.Create the new measure to calculate the monthly variation.

    Month Variation = 
var numb = SELECTEDVALUE('Table'[Part number])
VAR pre = SUMX(FILTER(ALLSELECTED('Table'),[Month] = MAX('Table'[Month]) - 1 && [Part number] = numb),[Monthly values])
VAR SELE = MAX('Table'[Month])
var result = [Monthly values] - pre
RETURN
result

    4.Drag the measure into the matrix visual.

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi slhyrkl ,

First of all, many thanks to ryan_mayu  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate the monthly values.

Monthly values = CALCULATE(SUM('Table'[Values]), FILTER('Table', 'Table'[Month]))

3.Create the new measure to calculate the monthly variation.

Month Variation = 
var numb = SELECTEDVALUE('Table'[Part number])
VAR pre = SUMX(FILTER(ALLSELECTED('Table'),[Month] = MAX('Table'[Month]) - 1 && [Part number] = numb),[Monthly values])
VAR SELE = MAX('Table'[Month])
var result = [Monthly values] - pre
RETURN
result

4.Drag the measure into the matrix visual.

5.The result is shown below.

Best Regards,

Wisdom Wu

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

  • slhyrkl's avatar
    slhyrkl
    Frequent Visitor
    2 years ago

    Hello Anonymous and ryan_mayu,

    I appreciate your replies. I tried it and it worked perfectly. One problem that I came across though is the subtotals. When I try to show the subtotals, for some reason, it's not adding upp the montly values and month variation properly in the table as I attached below.

    When I try it on the example you shared with me, it shows the total properly as attached below;

    I was wondering you may know what the reason could be behind it?