Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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!
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
please please help?
thank you
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
@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
how can I share an excel sample data?
hi,
tis calculated column is working and it's exactly what I need -
Column = CALCULATE(sum(measure)),FILTER('cards Test','cards Test'[week]='Rolling-week'[week]))
the problem is that i want the column to change dynamically by other slicer than date.
**there is a solution to multiple each week 4 times, and add column of the Rolling28 date related, but i am trying to avoid this costing solution
Hi,
It's my pleasure to help.
The column can't be changed dynamically by the slicer. The slicer works only in the report. Please post a sample here. You can upload the excel to online service like OneDriver or Dropbox, then share the link here. If these can't work, you can paste here directly with excel data.
Best Regards
Dale
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!
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
thank you!
this is the solution i tried, but i was mistaken with the relationship
thanks!!!
It's my pleasure. I am so glad your problem has been solved.
Best Regards!
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |