Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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.

 

 

Need Help
Show and Tell
  • bolfri's avatar
    bolfri
    3 years ago

    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:

    ProgramStatus Start DateStatus End Date
    A2022-01-012022-02-01
    A2022-01-052022-01-20
    A2022-01-102022-02-01
    A2022-01-152022-01-25
    A2022-01-202022-02-02
    A2022-01-252022-01-20
    B2022-01-012022-02-01
    B2022-01-052022-01-25
    B2022-01-102022-02-01
    B2022-01-152022-01-20
    B2022-01-202022-02-02
    B2022-01-252022-01-25
    C2022-01-032022-01-02
    C2022-01-082022-01-20
    C2022-01-132022-02-01
    C2022-01-182022-01-25
    C2022-01-232022-02-01
    C2022-01-282022-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. 🙂

     

     

8 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    Hey Anonymous ,

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      selimovd
      Icon for Most Valuable Professional rankMost Valuable Professional
      3 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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!