Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ashwinil
New Member

DAX Query help

Power BI Issue.PNG

 

 

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.
1 REPLY 1
v-shex-msft
Community Support
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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