Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mtozero
Helper I
Helper I

Power BI Table In the UI

We have a requirement to display the data in power BI with the below format. 

 

(a) Columns as Rolling 12 months,(show individual month), Previous 12 Months overall total, Current 12 Months and the variance of current and Previous 12 Months

(b) Average for each column 

(c) Color the column based on the Average. Note the column values should display values. 

 

 

 


Capture.PNG

1 ACCEPTED SOLUTION
14 REPLIES 14
freder1ck
Kudo Kingpin
Kudo Kingpin

Here's a pbi file that does most of what you are looking for, @Mtozero.

 

Sadly, it looks like my post where I explained how this works is in moderation....

 

Short version:

- disconnected table to setup the context for the report with a couple of calculated columns.

  • -11 to 0 rows are individual months
  • 1 is Trailing Twelve Months; 2 is Trailing Twelve Months last year; 3 is Variance.
  • Variance is the tricky one because it doesn't have just a start and end date.

Measures to get the appropriate amounts.

 

Create a matrix visualization with Range field from the Report table on Columns. 

States or in my case countries on rows.

Put the final measure on as values.

Turn off subtotals and grand totals for rows.

Keep grand totals for columns. 

 

Conditional formatting options are limited right now. 

 

disc report1.png

 

 

 

Thanks, @freder1ck, Appreciate your quick response. 

 

Do we have any options to dynamically change the column Name of the table?   

 

Thank you! 

 

When you say dynamically change the name of the column name, what exactly do you mean?

 

The column names of the Report table are set when the data is refreshed.

 

The current month (offset value 0) is 12/31/2017. In a few days, the current month will be 1/31/2018. 

 

It looks like I put the wrong link in to my personal OneDrive. I'll put the link up after work today.

 

Fred

 

 

 

thanks @freder1ck for your response. Appreciate your support. 

 

My problem is with the disconnected table since we need to more slicers. 

 

So what I was thinking let's assume there is no disconnected table. If I can add 12 measures (DAX for each month). It might work but the problem is how to make the measure name/column name dynamically changing.

 


@Mtozero wrote:

thanks @freder1ck for your response. Appreciate your support. 

 

My problem is with the disconnected table since we need to more slicers. 

 

So what I was thinking let's assume there is no disconnected table. If I can add 12 measures (DAX for each month). It might work but the problem is how to make the measure name/column name dynamically changing.

 



Could you describe a bit more the final result you're looking for? The results here can be sliced by Country, Territory, etc. You could even add a slicer on the disconnected table to show only specific columns. Are you looking to slice according to year and month instead of current state?

 

 

@freder1ck  Appreciate again your response. 

 

Attached the screenshot of what has to change properties dynamically.

 

 

 


@Mtozero wrote:

@freder1ck  Appreciate again your response. 

 

Attached the screenshot of what has to change properties dynamically.

 

 

 


I don't see the screenshot... By the way, I have a workaround for the conditional formatting you need. 

If you add a second measure that is the percentage of the whole, you can add that to your matrix and add conditional formatting of the background. If you don't want to see the numbers, resize the column to be really thin. You would need to turn off autosize for the columns. 

thanks, @freder1c Capture.PNGI want the name Month-Year has to be dynamically changed (Measure Name).  Can you share the example of conditional formatting 

You want to slice on Month Year? It sounds like 15 measures may be the way to go. You can still get the average in the total using Averagex.


And for the conditional formatting, I would do something like this. forums 20180105.png

 

 

Above or Below Average =
[Average Period Sales] / CALCULATE([Average Period Sales], ALL(Territories ))

 

Link

 

Good luck! Let me know how it goes.

 

Fred 

You should also set the sort by column for the Range column in the Report table. Sort it by the Value column. This way, the columns in the matrix will line up right.

 

As you dig into this file, please ask whatever questions you need to. 

 

Thanks,

 

Fred

freder1ck
Kudo Kingpin
Kudo Kingpin

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. 

disc report1.png

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

Ashish_Mathur
Super User
Super User

Hi,

 

I dont think this can be accomplished in PowerBI desktop.  This can be done in Excel using CUBE formulas.  Share the link from where i can download your base data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.