DAX Query help

[ashwinil]

PFA, I have the excel dataset as per show in first table of attached snapshot and I want to create power BI chart (table) to display Expense and Gross margin values.

I am not sure whether is there any way where I require to do calculation based on the values of columns.

[Anonymous]

Hi @ashwinil,

I'd like to suggest you enter to query editor to do unpivot column function on your fields to transform these value filed to attribute and value, then create a matrix visual with measure to achieve your requirement.('for P&L' to row, 'attribute' to column)

Unpivot columns (Power Query)

Sample formula:

Measure =
VAR _expense =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), Table[Attribute] = "Salary" ),
        VALUES ( Table[For P&L] )
    )
        - CALCULATE (
            SUM ( Table[Value] ),
            FILTER ( ALLSELECTED ( Table ), Table[Attribute] = "Travel" ),
            VALUES ( Table[For P&L] )
        )
RETURN
    IF (
        SELECTEDVALUE ( Table[For P&L] ) = "Expense",
        _expense,
        IF (
            SELECTEDVALUE ( Table[For P&L] ) = "Gross Margin",
            CALCULATE (
                SUM ( Table[Value] ),
                ALLSELECTED ( Table ),
                VALUES ( Table[Attribute] )
            )
                - _expense,
            CALCULATE (
                SUM ( Table[Value] ),
                ALLSELECTED ( Table ),
                VALUES ( Table[Attribute] ),
                VALUES ( Table[For P&L] )
            )
        )
    )

BTW, I have found any expense records in your table, did it real existed in your table? If not, you need to create a custom P&L table with related values, then use that value for calculate.

Regards,

Xiaoxin Sheng