Showing budget column once in result matrix

[jmkvalsund]

Hi,

I have a matrix showing sales result(values) pr industrycode (rows) and quarter (columns), all from table "sales". Works fine. Then I have a quite static table "budget" containing industrycode (one-to-many relation on sales.industrycode) and budgetamount. I want budgetamount to be shown once for evry industrycode in the matrix, preferably between industrycode and quarterly sales (budget being the same all over the year).

But if I put it in the matrix values-section, it shows up for each quarter. If I put it in rows it shows up beneath each row of industrycode, behind a + sign.  Should be quite easy, but neither I or Copilot can make this work. Anyone here who can?

Regards,

John Martin

[jmkvalsund]

Just a last question @Anonymous 😊
In my real database, the values are not sales and budgets, but financial factors (equivalent to "sales") on each industrycode, shown in the matrix as "percentage of column total" and the equivalent to budget is their max-value, or "ceiling". That means the column budget in my sample should be shown "as-is", not as percentage. Is that possible at all when both budget and sales are calculated by the same measure? I understand it cannot be formatted in the matrix-visual, but perhaps in the formulas in the SWITCH statement?
My example would then look like this (look away from the fact the budget column doesnt make sense, should have been a total of 100)

Best regards,

John Martin

[jmkvalsund]

Here's a very simplified model of the sales and budget table, the result in the matrix is still the same..
Sales

Budget:

Relation:

And just to visualize, this is how I want to look:

I hoped this should be rather easy, cause I'm in the making of a report with a loth of matrixes like this...
But Power BI doesn't want it that way 🙂

[Anonymous · Accepted solution]

Hi, @jmkvalsund 

Based on the example data you provided, I build a model similar to yours:

First, use enter data to create the following table:

Then establish the following two measures:

Measure = 
VAR _current_type = SELECTEDVALUE(Header[Index])
RETURN SWITCH(TRUE(),
    _current_type = 1,MAXX(FILTER('Budget','Budget'[Budget]= SELECTEDVALUE(Budget[Budget])),'Budget'[Budget]),
    _current_type>=2,SUMX(FILTER('Sales','Sales'[Industrycode]=SELECTEDVALUE(Budget[Industrycode])&&'Sales'[quarter]= SELECTEDVALUE(Header[Type])),'Sales'[sales])
)

Total version = SUMX(VALUES(Budget[Industrycode]),[Measure])

Create a matrix:

Best Regards

Jianpeng Li

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

[jmkvalsund]

Thanks a lot, @Anonymous !
This is exactly what I want!

Now I'll just sit down and understand your measures, and transform them to my real data!
Thanks again!
Best regards,

John Martin

[jmkvalsund]

@bhanu_gautam 
Sorry, I tried your suggestion, but I still get budget besides sales for every quarter. And I also get just the highest budgetamount (MAX(budget[budgetamount]) on all the rows, so the relation on industrycode is broken.

[bhanu_gautam]

Can you share some sample data

[bhanu_gautam]

@jmkvalsund 

In your data model, create a new measure for the budget amount. This measure will be used to display the budget amount only once per industry code.

BudgetAmountMeasure =
CALCULATE(
MAX(budget[budgetamount]),
ALLEXCEPT(sales, sales[industrycode])
)
Add the measure to the matrix:

In your matrix visualization, add the BudgetAmountMeasure to the values section. This will ensure that the budget amount is displayed only once per industry code.