Forum Discussion

cjsund's avatar
cjsund
New Member
1 year ago
Solved

Merging multiple TotalYTD visualizations.

Hi everyone,

 

I have a tracking sheet for 4 different stages of work, and each stage has it's own respective totalYTD visual.  But now I want to have a single visual that shows all 4 stages on one graph, but I can't figure out how to pull all the data onto one graph because they all have a unique date source for the X axis.

I was able to get the individual visuals to work with 

 

DR Status YTD =
IF(
    ISFILTERED('Master List'[DR Completion]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(
        COUNTA('Master List'[DR Status]),
        'Master List'[DR Completion].[Date]
    )
)

 

The above is an example of 3 of the different totalytd charts that I would like to merge into one visualization and then filter out by "Completed" from their respective columns.  The following chart being an example of what the tracking data looks like.

 

AR StatusAR CompletionCR StatusCR CompletionDR StatusDR CompletionWI statusWI Completion
Review21-Augrevisions24-Aug    
Complete02-Augcomplete05-Augcompleted22-Augrevisions27-Aug
Revisions21-Augin progress26-Aug    
Complete13-Augcomplete19-Augreview23-Aug  
Review20-Augreview24-Aug    
Hold       
In Progress25-Augin progress30-Aug    
Pending03-Seppending06-Sep    
Pending06-Seppending09-Sep    
Revisions23-Augrevisions26-Aug    
In Progress30-Augin progress02-Sep    
Pending30-Augpending03-Sep    
Pending29-Augpending01-Sep    
Pending06-Seppending09-Sep    
Revisions23-AugIn progress25-Aug    
Revisions22-Augpending24-Aug    
Revisions20-Augrevisions23-Aug    
Pending30-Augpending03-Sep    
Pending06-Seppending09-Sep    
review15-Augreview20-Augon hold   
complete23-Julcomplete26-JulRevisions09-Aug  

 

 I've run out of ideas how I can try to fit these all onto a single visualization just so I can have a graph showing the cumulative progress across all complete projects.  As this will encompass hundreds of rows eventually.

 

Thanks for the help!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from amitchandak , please allow me to provide another insight:

    Hi  cjsund ,

    I created four tables and four corresponding measure:

    Here are the steps you can follow：

    If you want to display the values of all four line charts on the same axis, you can simply create a table of dates to be used as a bridge table with the dates of the other four tables in a join relationship.

    Using the Line chart, use the [Date] of the date table for the X-axis, and place the four measures in the Y-axis.

    If you want to display different four line charts in the same visual, you can try to create a Group_Table and place it in Small multiples using [Group] as a category.

    Enter data – create a table.

    Create measure.

    Measure =
SWITCH(
    TRUE(),
    MAX('Group_Table'[Group])= "Measure1",[Measure1]
    MAX('Group_Table'[Group])= "Measure2",[Measure2],
    MAX('Group_Table'[Group])= "Measure3",[Measure3],
    MAX('Group_Table'[Group])= "Measure4",[Measure4]
)

     

     

    Best Regards,

    Liu Yang

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    cjsund , With help from date table, create a measure like below. And use status as a legend on the visual. Having a separate date/calendar table is important. Join date of Date table with the date of your table and use columns from date table in measure, visual and slcier

     

      TOTALYTD(
            COUNTA('Master List'[DR Status]),
            'Date'[Date]
        )
     
     
    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from amitchandak , please allow me to provide another insight:

    Hi  cjsund ,

    I created four tables and four corresponding measure:

    Here are the steps you can follow：

    If you want to display the values of all four line charts on the same axis, you can simply create a table of dates to be used as a bridge table with the dates of the other four tables in a join relationship.

    Using the Line chart, use the [Date] of the date table for the X-axis, and place the four measures in the Y-axis.

    If you want to display different four line charts in the same visual, you can try to create a Group_Table and place it in Small multiples using [Group] as a category.

    Enter data – create a table.

    Create measure.

    Measure =
SWITCH(
    TRUE(),
    MAX('Group_Table'[Group])= "Measure1",[Measure1]
    MAX('Group_Table'[Group])= "Measure2",[Measure2],
    MAX('Group_Table'[Group])= "Measure3",[Measure3],
    MAX('Group_Table'[Group])= "Measure4",[Measure4]
)

     

     

    Best Regards,

    Liu Yang

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