cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## DAX Query help

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.
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors