How to use both dynamic and static columns in table or matrix?

[StaceyLGriffeth]

Hello brilliant Power BI minds!

I have a report request that I'm struggling to create.  The request is to have a table or matrix with both dynamic and static columns.  The user would select a month from a slicer, and the table would update to reflect the metrics for each week of the selected month (e.g. 4-5 columns), followed by static columns that represent measures for the entire month (e.g. monthly average).  The linked pbix has sample data.  "MetricMeasure" is the measure which would be dynamically represented for each week of the selected month.  Any suggestions for how to accomplish this?

Sample_data.pbix 

Thank you!

[Greg_Deckler]

@StaceyLGriffeth OK, so many things. First, you don't need to wrap your calculations in CALCULATE. Second, by using FORMAT you are returning blank strings in the measure so that's bad since the columns would never go away because something is being returned. Third, what you are asking for is extremely difficult but possible. See the attached PBIX for the solution. I implemented this for 2 measures, you can follow the pattern for the rest of them if the solution is acceptable. First, you turn your initial VAR's into individual measures like:

Selfie Transactions = SUM('Transactions'[MatchSelfie Count])

Verified = SUM(Transactions[Verified Count])

No need to wrap those in CALCULATE at all.

Next, create a separate table for your month slicer:

Slicer = 
{ 
    ( "January", 1 ),
    ( "February", 2 ),
    ( "March", 3 )
}

Set your Sort By column for Month to MonthSort. Make sure that there are no relationships between this table and anything else.

Next, create a table for your Columns like this:

Columns = UNION(DISTINCT('Transactions'[WeekEndDate]), { "Monthly Average", "Monthly Max" })

Again, this should be a completely disconnected table. This contains all of your "dynamics" columns plus your "static" columns (Monthly Average, Monthly Max for example).

Now you create a measure like this:

MetricMeasure 2 = 
    VAR month = SELECTEDVALUE(Slicer[Month], "None")
    VAR monthnum = SELECTEDVALUE('Slicer'[MonthSort], 0)
    VAR week = MAX('Columns'[WeekEndDate])

    VAR __First = FIND("/",week,1,-1)
    VAR __Second = IF( __First = -1, -1, FIND("/", week,__First + 1,-1))
    VAR __Date = IF( __First = -1 || __Second = -1, BLANK(), DATEVALUE(week))

    VAR __Table = 
        SUMMARIZE(
            FILTER('Transactions', MONTH([WeekEndDate]) = monthnum),
            [WeekEndDate],
            "selfietrx", [Selfie Transactions],
            "verified", [Verified]
            )

    VAR __Result = 
        SWITCH (
        TRUE (),
        month = "None", BLANK(),
        week = "Monthly Average", 
            SWITCH(TRUE(),
                MAX('ReportConfig'[var]) = "selfietrx", AVERAGEX(__Table, [selfietrx]),
                MAX('ReportConfig'[var]) = "verified", AVERAGEX(__Table, [verified])
            ),        
        week = "MOnthly Max", 
            SWITCH(TRUE(),
                MAX('ReportConfig'[var]) = "selfietrx", MAXX(__Table, [selfietrx]),
                MAX('ReportConfig'[var]) = "verified", MAXX(__Table, [verified])
            ), 
        SWITCH(TRUE(),
                MAX('ReportConfig'[var]) = "selfietrx", SUMX(FILTER(__Table, [WeekEndDate] = __Date), [selfietrx]),
                MAX('ReportConfig'[var]) = "verified", SUMX(FILTER(__Table, [WeekEndDate] = __Date), [verified])
            )
        )
RETURN
    __Result

 And viola! Oh, and you can set your formatting on your MetricMeasure 2 measure once to the format you desire or use a custom format string. They were all the same format anyway.

[StaceyLGriffeth]

Hi @Greg_Deckler thank you for the incredibly thorough reply!  I'm working through applying it to my data, now.  A couple questions:

1)  As the data will cross years, the month slicer will need to include year.  So instead of make the values January - December, should I just make it a longer table with each month/year combination for the next several years?

2) I had summarized my sample data by week ending, but in my live data it is by the day.  So for each selected month, it should also include the week ending dates that straddle months, but only include the totals for the days in the selected months.  Do I need to make any adjustment for this?

Thanks again!

Stacey

[Greg_Deckler]

@StaceyLGriffeth Well, the short answer is that if the data model changes then yes there need to be changes made to the solution. For example, you will have to decide how you want your month/year slicer to work. Likely three columns, month, year and monthyearsort so:

January, 2022, 202201

You could use that in a hierarchy in a slicer for example. But then you need to grab the selected value for month and year and then check your date or weekending date.

For the second item, I would need an example of what you mean.

[StaceyLGriffeth]

Hi @Greg_Deckler I've updated the pbix (Sample_data_v2.pbix  ) to reflect the way I would want to use the slicer (MonthYear) and to include data by day, rather than grouped by week.  So regarding my question #2 previously.... for example, Transactions[WeekEndDate] = 01/05/23 contains Date values 12/30/22 - 1/5/23.  If I select December 2022 from the Month Slicer, it should include week ending 1/5/23, but only data through 12/31/22 would be calcuated.  When January 2023 is selected, it would include week ending 1/5/23, but only data from 1/1/23 on would be calculated.  Does that make sense?