How to Compare the Values in Two Different Visuals and Create a New Third Visual

[OngS2]

Hi all,

Appreciate if you're able to assist my following question.

I'm having two matrices as A and B. I formed them by using the raw data.

Now, I'm having an idea to take the consolidated data in the respective matrix to form the third visual. Is it possible to do that? And  how should I do that?

For example, I need the 19.92 in A and 11.90 in B to get a percentage by dividing B by A. And apply the same to the rest of the data.

I was trying to calculate the 19.92 and 11.90 in "Data" pane, but I failed to do so due to the huge data and long formula needed.

Please guide me if you have an idea. Thank you.

[OngS2]

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?

[selimovd]

Hey @OngS2 ,

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

[OngS2]

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!

[bolfri]

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.

[OngS2]

@bolfri @selimovd 

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!

https://we.tl/t-ZekbLXTvoz 

[bolfri · Accepted solution]

@OngS2,

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. 🙂

[selimovd]

Hey @OngS2 ,

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

[selimovd]

Hey @OngS2 ,

if it's the same filter context you can just refer to the measure.

For example:

Measure Pct = DIVIDE( [Measure A], [Measure B] )

Try before to add both measures to the matrix, to see if they return the result you expect.

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