Dynamic Matrix with Rows Selection (with Parameter) + Rows Manipulation

[artur4]

Hello,

I am trying to create a Matrix whose rows can be changed by a slicer which is linked to a Parameter (in this I have several Categories).

Since I have two different tables, I need to calculate the difference and show it as an Adjustment in the Matrix.  In one table I have the sales till  customer level while in the other till country level (differnt data sources).

I have to:

• show the values from Table1 (the one with customers) in the rows
• show on the Adjustment row the delta between Table1 and Table2
• the total has to be Table1+Adjustment

To do so, I have:

• addedd Adjustment label under customer in one to the tables (Table2)
• added Data Source column to filter in the measure
• appended the two queries to create the final table
• then I created another table with the same columns and filled it with Adjustment in every text column (these are the same columns I have in my parameter table to be shown in the Matrix) and 0 in the numerical ones. I replicated it for all the years. And appennded it to the final table

By the way, Adjustment has to be shown at the lowest level of my hierarchy (i.e., Customer).

My idea was to manipulate the values in the Matrix by using the code below; I already used it in another dashboard but without the Parameter in the rows, I used just a normal column of the main table and it has worked.

Sales m = 
var delta =  CALCULATE(SUM('Appended Table'[Sales]),'Appended Table'[Customer] = "Adjustment") - CALCULATE(SUM('Appended Table'[Sales]),'Appended Table'[Customer] <> "Adjustment")

var sales = CALCULATE(SUM('Appended Table'[Sales]), 'Appended Table'[Data source]<> "Tab2")
    return
            SWITCH(TRUE(), 
            SELECTEDVALUE(Parameter[Parameter Fields]) = "Adjustment", delta,
            COUNTROWS(VALUES(Parameter[Parameter Fields]))>1, sales+delta,
            sales)

From my understanding, this is not working as it cannot figure out the value "Adjustment" in the underlying column passing through the Paramenter.

I am sharing a PowerBi example so you can have a look. PowerBi Link 

Do you have an ideas? 

[artur4 · Accepted solution]

Hello, I close this post as I figured it out by myself.

Just needed to add an ALLSELECTED in the delta variable of my measure.

[artur4]

I checked once again and the issue does not seem to be related to the field parameters.

It seems that when I append the additional table I created with "Adjustment " in all the columns to the main one, I cannot manipulate that specific row with this part of the code: 

COUNTROWS(VALUES(Parameter[Parameter Fields]))>1, sales+delta,

I am doing like this because otherwise I'd need to replicate all the data in one of the tables (potentially Tab2) many times by adding the label Adjustment for each column for each combination. (e.g. Adjustment for Country and duplicate all the data to have the other columns cobinations, then again with market, etc.)

For the others labels, like FRANCE or POLAND, by using that code I can manipulate the number displayed.

You know why? How can I fix this?

[Idrissshatila]

Hello @artur4 ,

check if the field parameters would be a help in your case https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[artur4]

Hello @Idrissshatila, thaks for your reply.

I did not specify it, I am already using field paramters. This is how I can select different rows in my Matrix. Hoewver, I am not able to modify the rows and the total as I usually do by applying the logic in the code above. 

PS

In my original version I am using field parameters also for the columns (i.e., the values of the Matrix which are linked to measures).