Add dual value to Power BI Matrix Visual

[ChirSidh]

How to add two different values in a Power BI matrix visual, one at the project level and the other at the subproject level.

I have Project and Sub Project (Work order) in rows and month number in columns. What i want to do is add Project Level multiplier to Project row and Sub Project level multiplier to Sub Project row, but if i add two values in the values section, Matrix visual is creating different column fot the second value section, is there anyway by using DAX i can add different values to different level rows

[ChirSidh]

Hello everyone, anyone has solution for the problem i mentioned?

[v-yanjiang-msft]

Hi @ChirSidh ,

My solution is similar with @amitchandak 's, I create a sample to clarify it.

Sample:

If you want to show Column1 in Project and show Column2 in Sub_project, create a measure as below:

Measure =
IF (
    ISINSCOPE ( 'Table'[Sub_project] ),
    MAX ( 'Table'[Column2] ),
    MAX ( 'Table'[Column1] )
)

Put the measure in matrix Values, get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[ChirSidh]

Thank you , but there is a problem with this when i add multiple months in the columns as showin in my first message , for all the months it is showing the same number, i think it is just taking the max of sub project values.  please see my screenshot 

[v-yanjiang-msft]

Hi @ChirSidh ,

Are the [Work Order No] column and month column in the same table or are they related? If so, the MAX function will extract the current value for the pair, otherwise it will extract the max value.

Best regards,

Community Support Team_yanjiang

[ChirSidh]

Hi, they are in  different tables but they are related with Project number 

[v-yanjiang-msft]

Hi @ChirSidh ,

If they're related with project column, you put Work Order No column, month column and Current Forecast Multiplier column in a table visual, can they be displayed in the correct result? If not, please modify the relationship.

Best regards,

Community Support Team_yanjiang

[ChirSidh]

Hi, i cant put Work order in column as in my real data set i have more than 100 projects and 1000 WO's. So i need to keep Project and Work order in Rows only and Months in the Column as shown in my file

[v-yanjiang-msft]

Hi @ChirSidh ,

I don‘t mean for you to change the matrix visual, I just suggest you check if each Work Order-Month pair can get a Current Forcase Multiplier value. If not, you should modify the relationship.

Best regards,

Communtiy Support Team_yanjiang

[ChirSidh]

Hi, Can you show me how this is done in your PBIX file, i am not able to attach PBIX file here sorry 

[v-yanjiang-msft]

Hi @ChirSidh ,

Simply create a table visual and put Work Order column and Month column in it. If they are related, they will have some overlap in the same row. Otherwise the visual will broken.

Best regards,

Community Support Team_yanjiang

[ChirSidh]

Hi Amit, i already have a custom column created for Project Level Multiplier by grouping the Work order level multiplier to Project level in Power query.

and work order level multplier is already available in my spreadsheer.

 do i still have to create two new measures , i am sorry if this is a silly question, i am just new to Power BI so having a hard time to understand the video you have shared

[amitchandak]

@ChirSidh , calculate two different measure and switch using isinscope,

Switch(True(),

isinscope(Table[Work Order no]), [M1],

isinscope(Table[Project]), [M2],

[M3]

)

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials: https://youtu.be/smhIPw3OkKA

[ChirSidh]

ANyone help please?