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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello,

Thank you very much for your effor. I appreciate.

Additionally, Can I have a table like this:

1= 512 order

0= ?     orders?

 

1 means spike orders

0 means none-

Thank you 

Baris

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

actually I want to count orders which are spikes or none 

 

Thanks you

Baris 

  • 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

  • mariussve1's avatar
    mariussve1
    Solution Sage
    2 years ago

    Hi again,

     

    Yes this is also possible. If youre gonna use 1 and 0 as dimension and sum the result I think its better to create a new calculated table instead.

     

    Is that ok? If so I can send you an updated model 🙂

     

    Br

    Marius

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    it will be great for me. May you send me the updated model ? ğŸ™‚

    Thank you in advance

    Baris

  • 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