Forum Discussion

MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
Solved

problem with calculation-many to many

hi

i have a problem with calculation of a measure

my final table looks like

Orig          Date               measureX

1            02/25/2017             2

1            03/04/2017             24

1            03/11/2017             223

1            03/18/2017             12

2            02/25/2017             250

 

orig=1 means weekly data, orig=2 means Rolling28 data

i want to have a slicer of date, and want to show the 4 related weeks. i.e if i select 2/25 on the slicer, the chart will show the 4 weeks on x axis and y will be the measure for each week.

the problem that i can't manipulate the data since the slicer sliced my table with 2/25 only (orig 1 and 2)

the solution here is many to many relationship with this table Rolling-Week:

Rolling          Week

2/25/2017      2/25/2017

2/25/2017      3/4/2017

2/25/2017      3/11/2017

2/25/2017      3/18/2017

3/4/2017         3/4/2017

3/4/2017         3/11/2017

3/4/2017         3/18/2017

3/4/2017         3/25 /2017 . . .. etc

but the problem is that there is no way to manage many to many relationship in power bi

so: i created the dates table: 

Date

2/25/2017

3/4/2017

 

 . . .

and connected it one to many with the rolling-week table from above.

now, if I slice on 2/25/2017, I know what are the 4 weeks related.

the problem is - calculated the measure for each week: MEASURE = 

CALCULATE (
sum'Final Table'[Measure X]),
(filter(ALL('Final Table'[week]),'FinalTable'[week]=max('Rolling-week'[Week]))
))

the problem is that i have to add an aggregation to the 'Rolling-week'[Week])

how can i calculaure the measure for EACH week?

 

thanks a lot

 

 

  • MP_123's avatar
    MP_123
    9 years ago

    v-jiascu-msft

    hi Dale,

    thank you very much!

    this is the sample

     

    reminder: orig=1 means weekly data: 04/01 is 04/01 to 04/07

    orig=2 means 28 days - 04/01 is 04/01-04/28

     

    thanks!

     

  • MP_123

     

    Hi,

     

    I am so sorry I still can’t make out the whole thing. But we can solve it step by step. I made a little mode with your sample.

    1. Is this similar with yours?

    2. Which columns in your sample are to be used?

    3. What does the "measure X" mean? Can you post the formula if it has one.

    4. Please have a look at the image in the bottom. Is this what you want? Or you can privide a sample visual.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

     

     

10 Replies

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee

      MP_123

       

      Hi,

      Could you post a little sample of your source data please. I have some questions about this:

      1. Is the “Final Table” your source data or the result you want.

      2. What is sum'Final Table'[Measure X]) for.

      3. Is MeasureX a measure or a calculated column.

       

      Best Regards.

      Dale

      • MP_123's avatar
        MP_123
        Icon for Microsoft Employee rankMicrosoft Employee

        v-jiascu-msftthsank you!

        above is a little sample, if it's not enought i will share the whole table.

        the final table is my source table, the measures i want to some is there.

        measure X is a calculated measure

        2. i want to have bar chart where X axis is the 4 weeks related, and Y axis is the measure

         

        thanks again