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.

If you want to aggregate this on ordernumber, as I see that you might want, then the measure will be a little more advanced. Then we have to aggregate it in a variable, and then run sumx like this.

Measure = --IF(SUM(PMD[Maximum order quantity]) < SUM(Sheet1[BALANCE QTY]),1,IF(SUM(PMD[Maximum order quantity])>SUM(Sheet1[BALANCE QTY]),0))
VAR __VTable =
    SUMMARIZE (
        Sheet1,
        Sheet1[ORDER_NO],
        "Balance Qty", SUM ( Sheet1[BALANCE QTY] ),
        "Maximum order quantity", SUM ( PMD[Maximum order quantity] )
    )
VAR __VTable2 =
    ADDCOLUMNS (
        __VTable,
        "Measure",
            IF (
                [Maximum order quantity] < [Balance Qty],
                1,
                IF (
                    [Maximum order quantity] > [Balance Qty],
                    0,
                    BLANK ()
                )
            )
    )
VAR __Result =
    SUMX (
        __VTable2,
        [Measure]
    )
RETURN
    __Result


Then the total returns 512, more like you want I guess?

 
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,

       

      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