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

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

as I understand I can not do by using the relations as I do basically. I have to learn calculated tables.

I appreciate you. Thank you very much for your effort.

Thanks

Baris