how can we custom calculation at row level in Matrix Visual in power BI Desktop

[amty63]

Hi All,

I need to achieve the below result in MATRIX visual where I have measures like "Actual", Budget etc... with the ITEM_ID join to dimension table.... 

I don't have information like "Structure", "Productive", "Unpaid Leaves" etc...

How can we calculate these below rows when in few rows we have same ITEM_ID ... we can't use IF ELSE expression here..

 Please help.

[v-alq-msft]

Hi, @amty63 

Based on your description, I assume that you have three levels and want to calculate 'Actual' for level 1, 'V2' for level 2, 'Budget' for level 3. I created data to reproduce your scenario.

Table:

You may create a measure as below.

Result = 
IF(
    ISINSCOPE('Table'[Level 3]),
    SUM('Table'[Budget]),
    IF(
        ISINSCOPE('Table'[Level 2]),
        SUM('Table'[V2]),
        IF(
            ISINSCOPE('Table'[Level 1]),
            SUM('Table'[Actual])
        )
    )
)

Result:

Best Regards

Allan

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

[amty63]

Thanks Allan for reply, 

However, my requirement is to calculate rows like Structure, Productive, Unpaid leaves, Holidays etc based on item IDs in the fact table.. I have all three facts information in fact table but problem is to calculate Structure, Productive in row whereas some of the itemI Ds are common in these rows..

Hope you understand the requirement..

[amty63]

Hi Allan,

My question is How will I make Hierarchy based on item IDs  becuase ITEM IDs are common in few cases.. Please refer scrrenshot attached in the question.

[amty63]

I am still waiting of reply. Still looking for the solution..

[amitchandak]

@amty63 , this format is difficult to get. Are you looking for the paginated report?

https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-report-builder-power-b...

What is format of Source data

[amty63]

what I need to do .. calculate worked days, Structure, Productive on the basis of item_id .. ITEM_ID in the excel for the condition to calculate the measure..

Like IF ITEM_ID =520/410 then Structure IF IITEM_ID= 550/410 then UNPAID LEAVES

[amty63]

Not for paginated report..

Format is normal.. in tables ... but the problem is calclulation at row leavel... is it possible on power BI desktop through any visuals..