Power BI Table In the UI

Hi Mtozero,

It's possible to get most of this done in Power BI, using a disconnected table that will create the special report form.

Conditional formatting is a bit limited still. 

There are 3 steps to this.

1. Create a disconnected table

2. Create some measures to populate the table.

3. Put your measure into an AVERAGEX() so that the total will display as the average of the column.

Disconnected table

From the Modeling tab of Power BI, select the New Table button. Create the table by putting the following into the formula bar.

Report = GENERATESERIES(-11, 3, 1)

This creates a table of values from -11 to +3 (incrementing at 1).

• -11 to 0 will be the actual months.
• 1 will be TTM.
• 2 will be TTM LY3 will be Variance.

We need an end date and a start date for each row of this table to filter the sales data. 

Then, add some columns to your Report table.

End Date =
IF (
    Report[Value] < 1,
    EOMONTH ( TODAY (), Report[Value] ),
    IF (
        Report[Value] = 1,
        EOMONTH ( TODAY (), -1 ),
        IF ( Report[Value] = 2, EOMONTH ( TODAY (), -13 ), EOMONTH ( TODAY (), -1 ) )
    )
)

The second column is Start Date

Start Date =
IF (
    Report[Value] < 1,
    DATE ( YEAR ( Report[End Date ] ), MONTH ( Report[End Date ] ), 1 ),
    EOMONTH ( Report[End Date ], -12 ) + 1
)

Start date is the beginning of the month for actual months

For other periods, it's 12 months back.

You can verify the date periods in the data view of the Report table (and change as needed).

Range =
IF (
    Report[Value] < 1,
    FORMAT ( Report[End Date ], "" ),
    IF ( Report[Value] = 1, "TTM", IF ( Report[Value] = 2, "TTM LY", "Variance" ) )
)

This is the last column added to the table. It's the name of the column. This will go onto rows in a matrix visualization.

The basic measure is PdSales. PdSales is the calculation for all ranges except Variance. 

Total Sales = SUM(Sales[Amount])

PdSales = CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            'Dates'[Date],
            MIN ( Report[Start Date] ),
            MAX ( Report[End Date ] )
        )
    )

Period Sales measure switches between PdSales and Variance.

Period Sales  =
IF(MAX(Report[Value]) < 3,
    [PdSales],
    [Variance]
)

Variance is TTM - TTM LY

Variance =
    CALCULATE([PdSales],
  ALL(Report),
  Report[Value]=1)
-
CALCULATE ([PdSales],
 ALL(report),
 Report[Value]=2
        )

The third thing we need is the AVERAGEX to make the totals come out as averages.

Average Period Sales = AVERAGEX(Territories, [Period Sales ])

To build your matrix, put Range field on columns and Territory (aka State) on rows.

Turn off subtotals and row totals. Keep column totals on.

Thanks,

Fred Kaffenberger
Kansas City Metro

And here's a OneDrive link to the pbix