Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measure with IF function between 2 reports

Hello, I have created a measure between 2 different report in Power BI. The measure based on If function. as you can see on the blow I'd like to count spike and none-spike orders. but the problem is...
Need Help
  • mariussve1's avatar
    mariussve1
    2 years ago

    Hi again,

    I have now sendt you an updated model.
    What I did as agreed was to create a new calculated table:

    Calculated table = --You can change this to the name you want of the table
    VAR __VTable =
        SUMMARIZE (
            Sheet1,
            Sheet1[ORDER_NO],
            "Balance Qty", SUM ( Sheet1[BALANCE QTY] ),
            "Maximum order quantity", SUM ( PMD[Maximum order quantity] )
        )
    VAR __Result =
        ADDCOLUMNS (
            __VTable,
            "Measure",
                IF (
                    [Maximum order quantity] < [Balance Qty],
                    "Spikes", --This you can change to what you want
                    IF (
                        [Maximum order quantity] > [Balance Qty],
                        "None spikes", --This you can change to what you want
                        BLANK ()
                    )
                )
        )
    RETURN
        __Result


    Then you can just create a new measure counting:

    Rows = --this can be changed to what you wnat
    COUNTROWS( 'Calculated table' )


    If you found my post valuable, please give me a Kudos and click 'Accept solution' button 🙂

    Br
    Marius

mariussve1's avatar
mariussve1
Solution Sage
2 years ago

It is possible to do this with two measures also if you like that better than calculated table.

 

But when it comes to dimensions lile this i think its best to use calculated table.

 

The calculated table is, however, dependent on the other two and the relationship between them to function.

 

Marius 🙂