Advanced Matrix

[vikramsharma18]

Is this matrix Possible. Here are the Rows values of Net Sales, covers, Average Spend per hour, Provisional Labour Cost, % Of Sales, and Provisional Labour Hours.
Columns Will be Mon to Sun and WTD, Last Year's WTD, Variance with Last year's WTD,% of Variance with Last year's WTD, WTD Budget, Variance with WTD Budget,% of Variance with WTD Budget.
How to Solve this?

[AlexisOlson]

Here are some additional approaches:

https://stackoverflow.com/questions/52632713/custom-aggregate-column-in-power-bi-matrix

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...

[danextian · Accepted solution]

Hi @vikramsharma18 ,

There are several approaches to your use case

• calcualation groups
• field parameters
• a custom visual (there is one but needs subscription if used in the web based on the number of users)
• or some advanced DAX with the use of disconnected tables and several to a handful measures -  i used to have actual similar use cases in the past the solution wasn't exactly the fastest.  Here's a screenshot of what I did

  I can't share with you the pbix as the data is confidential and the solution took some time to come into fruition. But here's a sample pbix of a similar but very simple use case.  https://1drv.ms/u/s!AqxKG9R1145foQvISJkkuSEpEUQy?e=5YwIdj 
  Notice in that first column chart below the total is one of the columns. The second chart is how it is normally done.

  

[lukiz84]

Good post! The easiest way for him would be calculation groups, it's also the one solution which requires the least effort and no custom visuals.

[lukiz84]

Possible with calculation groups where you add "Mon", "Tue", ... "Week to date", "Last year WTD" ,... "var %" as calculation items for your Measures ([Net sales], [Covers] and so on).

See Calculation Groups - SQLBI