Forum Discussion

isaki's avatar
isaki
Regular Visitor
3 years ago

How to create a measure

Hello, Sorry this may be simple but I am new to Power BI. I need to count the number of sales of the target items in the sales report like below. (colored cells) The target Items is basically A, b...
Need Help
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

 

 

 

 

 

 

Target measure: = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            'Order',
            'Calendar'[Fiscal Year],
            'Calendar'[Year-Month],
            'Calendar'[Year-Month Sort],
            'Item'[Order Item],
            'Order'[Order ID]
        ),
        "@Target",
            VAR _fy = 'Calendar'[Fiscal Year]
            RETURN
                IF (
                    CALCULATE ( MAX ( 'Item'[Order Item] ) )
                        IN SUMMARIZE ( FILTER ( Target, Target[Fiscal Year] = _fy ), Target[Target] ),
                    1,
                    0
                )
    )
RETURN
    SUMX ( _t, [@Target] )