Forum Discussion

jfrench's avatar
jfrench
Regular Visitor
2 years ago
Solved

YoY Variance Visualization with data on unique tables

Hi, thanks in advance for help to solve this one- I keep getting stumped on how to setup relationships to make a variance work. I do not want to combine these data tables. I want them to stay separate. 

 

I have two tables, one which accounts for 2023 project and another for 2024 projects. Each project has an associated project LOE, go live date and fiscal quarter for when most of the work is occuring. I'm trying to create a visual that will display a variance so that my team can account for bandwidth and projects needing more support in one quarter opposed to the balance of the year.

 

I added a reference table and created relationships from the 2023 Table to the Reference Table. and did the same with the 2024 Table. But not having any luck, I can't figure out how to get my visual to display that there were 3 high LOE projects in q1 of 2023 compared to only 1 Medium LOE project in 2024, or a 67% decrease in total projects happening in quarter over quarter. 

 

2023 Table

Project NameGo Live DateFiscal QuarterProject LOE
2023ProjectAJanuary 1 2023Q1High
2023ProjectBJuly 15 2023Q3

Low

2023ProjectCJanuary 10 2023Q1

High

2023ProjectDMarch 15 2023Q1

High

2023ProjectEAugust 30 2023Q3

High

 

 

2024 Table

Project NameGo Live DateFiscal QuarterProject LOE
2024ProjectFFebruary 2 2024Q1Medium
2024ProjectGJuly 1 2024Q3

High

 

Reference Table

Fiscal QuarterProject LOE
Q1Low
Q2Medium
Q3High
Q4 
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,jfrench I am glad to help you.

    Based on your description, you want to calculate the change in PROJECTS for each quarter between two years

    For example the number of projects in the first quarter of 2024 is down 67% compared to 2023

    If I understand you correctly, you can refer to my test below

    I have constructed three measures and show them on a line chart visual.
    like this：

    Here is the DAX code

    M_preProAmount = 
CALCULATE(
COUNT('2023_Table'[Project LOE]),FILTER(ALL('2023_Table'),'2023_Table'[Fiscal Quarter]=MAX('2023_Table'[Fiscal Quarter])))

    M_thisProjectAmount = 
CALCULATE(
COUNT('2024_Table'[Project LOE]),FILTER(ALL('2024_Table'),'2024_Table'[Fiscal Quarter]=MAX('2024_Table'[Fiscal Quarter])))

    M_result = 
DIVIDE([M_thisProjectAmount]-[M_preProAmount],[M_preProAmount],0)

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,jfrench I am glad to help you.

    Based on your description, you want to calculate the change in PROJECTS for each quarter between two years

    For example the number of projects in the first quarter of 2024 is down 67% compared to 2023

    If I understand you correctly, you can refer to my test below

    I have constructed three measures and show them on a line chart visual.
    like this：

    Here is the DAX code

    M_preProAmount = 
CALCULATE(
COUNT('2023_Table'[Project LOE]),FILTER(ALL('2023_Table'),'2023_Table'[Fiscal Quarter]=MAX('2023_Table'[Fiscal Quarter])))

    M_thisProjectAmount = 
CALCULATE(
COUNT('2024_Table'[Project LOE]),FILTER(ALL('2024_Table'),'2024_Table'[Fiscal Quarter]=MAX('2024_Table'[Fiscal Quarter])))

    M_result = 
DIVIDE([M_thisProjectAmount]-[M_preProAmount],[M_preProAmount],0)

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jfrench's avatar
      jfrench
      Regular Visitor
      2 years ago

      Hi Carson Jian,

      Thank you for taking a look at this for me. I wrote the first 2 DAX in Power BI but the results are not what I expected.  The count of projects is not respecting the defined quarter as I had hoped.

       

      These are the counts I expect to see and be able to work with in the same visualization so that I can create a variance view

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,jfrench .Thank you for your reply.
        According to your description, you didn't get the correct result when trying the test code I gave you, which may be caused by the difference of the computing environment (in fact, the data I tested is very simple and can't completely replace your real computing environment)
        Please check if the visual in which the measure is used is affected by other filters (such as slicers or field filters) and write the DAX code according to your real situation.

        Also if you want to show the data of the same field in two tables in one visual, it's not as convenient as combining two tables into one (you may need to write more than one measure).
        If you could provide more data about this, it would be very helpful to solve your problem.

        I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

        Best Regards,

        Carson Jian,

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Why do you not want to append the 2 tables?  Show the expected result clearly.