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

Hi,

actually I did not turned it off. I will appreciate if you share your pbix file in any way?

Thank you

Baris 

mariussve1's avatar
mariussve1
Solution Sage
2 years ago

It would be better if you could please share me a example file? Then I can edit the measure and send it back to you. Else I need to create a example model, and I dont have all the details.
Do you have dropbox, onedrive or something? If so, please send me a DM and I will look into it for you 🙂

Br

Marius

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello,

    I have just shared the link with you

    Thanks in advance

    Baris

    • 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