Forum Discussion
How to Compare the Values in Two Different Visuals and Create a New Third Visual
Anonymous,
It's not a sample data. It's a real data that shows problem with understaning how Power BI works. Just by looking at number of columns that represent month. 🙂
1 Step. Get rid of all unnessesery columns in your data without losing any information that you need. This step includes deleting column with year, month, week etc for each date that you have. You can save 2 columns for rach date meaning eg start_date, start_datetime (if time is needed, if not leave only date).
This is the sample thata on which I will explain you your case:
Program Status Start Date Status End Date A 2022-01-01 2022-02-01 A 2022-01-05 2022-01-20 A 2022-01-10 2022-02-01 A 2022-01-15 2022-01-25 A 2022-01-20 2022-02-02 A 2022-01-25 2022-01-20 B 2022-01-01 2022-02-01 B 2022-01-05 2022-01-25 B 2022-01-10 2022-02-01 B 2022-01-15 2022-01-20 B 2022-01-20 2022-02-02 B 2022-01-25 2022-01-25 C 2022-01-03 2022-01-02 C 2022-01-08 2022-01-20 C 2022-01-13 2022-02-01 C 2022-01-18 2022-01-25 C 2022-01-23 2022-02-01 C 2022-01-28 2022-01-20 Step 3. Create a calendar that holds all the dates in your model. You can put exact dates here or let Power BI create a table based on the dates you have in your model.
dim_calendar = CALENDAR(FIRSTDATE('Sample'[Status End Date]),LASTDATE('Sample'[Status End Date]))Step 4. In dim_calendar table add new columns that representing year, month, quarter, week or what you need. In this scenario you'll have one month representation, not six-seven like in your case. It will be easier for you to not make mistakes in the future.Year = YEAR([Date])Month = MONTH([Date])Week = WEEKNUM([Date])Step 5. Create relationship between each dates to the calendar. Notice thtonly one of them is active and it's Status Start Date to Date.Step 6. Create a measure that you need. In my case due to fact that I dont have any values I will be counting programs, but in your case it will be "sum of operation time".
Counting programs = COUNT('Sample'[Program])Step 7. Create more measures. I will call them Table1Measure and Table2Measure.Table 1 Measure calculates number of programs based on status start date.Table1Measure =CALCULATE([Counting programs],USERELATIONSHIP(dim_calendar[Date],'Sample'[Status Start Date]))Table 2 Measure calculates number of program based on startus end date.Table2Measure =CALCULATE([Counting programs],USERELATIONSHIP(dim_calendar[Date],'Sample'[Status End Date]))Step 8. Create another measure with KPI. This is what you've wanted to do in the first place.KPI = DIVIDE([Table1Measure],[Table2Measure])Step 9. Put the measures on the report. For Dates use only dim_calendar table eg weeks. In this scenario you can use multiple measures by one week fields and it will work perfectly. 🙂
Hi selimovd Denis,
Thank you for your prompt reply.
For the A and B, they aren't measures, but only being summed up according to the dates in the matrices.
They are only the raw data in my "Data" pane.
In this case, how can I use the values to do the third new visual?
Hey Anonymous ,
then you have to create an aggregation for them. For example if you want the sum of the column use:
MeasurePct = DIVIDE ( SUM ( myTable[Column A] ), SUM ( myTable[Column B] ) )
Would that work?
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hi selimovd ,
Might not able to do so. As my data is distributed based on dates.
For examples. A is the sum of operation time for all production IDs on the particular real end date.
B is the sum of operation time for all production IDs on the particular completion end date.
I was trying to sum up them by using the functions/commands, but system claimed to have too many arguments and unable to perform the calculation.
Any more ways to deal with this kind of data?
Thank you for your patience!
Hey Anonymous ,
I think that's a different topic. The one you asked here is how do you do a division in a single field. The solution is what I posted above.
The second is you have a more complex data model and you don't know how to calculate the measure properly in the first place. I personally would post that as a new topic. But we can also try to solve that here.
How does the data model look like? What DAX measure did you try and what was the error? What is the expected result and how would you calculate that in a correct way?
Best regards
Denis
Without sample data it's hard to write down a DAX for you to use it, but I've done this in different topic.
PRE
1. Real end date need to be a date
2. Completion end date need to be a date
CALENDAR
3. Create dim_calendar table to hold all the dates between min and max possible date
4. Connect dim_calendar by date to Real end date and set it inactive
5. Connect dim_calendar by date to Completion end date and set it inactive
MEASURE
6. Create "sum of operation time by real end" measure including USERELATIONSHIP() function in it between the Dates in dim_calendar and Real end date
7. Create "sum of operation time by completion" measure including USERELATIONSHIP() function in it between the Dates in dim_calendar and Completion end date
8. Create "difference" measure that is simply measure 1 - measure 2
Viz it using a Date from dim_calendar table.
If you share a sample data (only few rows needed), I can help you with that.
Hey guys, sorry for late reply. Wishing you & your family a belated Merry Christmas!
I come out with a datasheet as per attached link and stated the challenges and my expectation on the tab of "18 March 2022". Hope that you're able to solve my doubts.
Thanks!