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 I can see any order if it is spike or none but when it comes to count number of spike and none spike orders I can see total number of orders under spike. 

 

my formula is : 

Measure = IF(SUM(PMD[Maximum order quantity]) < SUM(Sheet1[BALANCE QTY]),"Spike",IF(SUM(PMD[Maximum order quantity])>SUM(Sheet1[BALANCE QTY]),"None"))
 

Can you help me to solve this problem ?

 

Thanks in advance.

Baris 

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

16 Replies

  • mariussve1's avatar
    mariussve1
    Solution Sage
    2 years ago

    Hi,

     

    When you use if and the last argument (or any argument) is a string: "None" you will not get a total. Totals only works on numeric values.

     

    Marius 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello Marius,

      you mean this:

      Thanks

      Baris

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi

        I have changed the strings ones to numeric one which you can see above. but still can not see totals. 

        Thanks

        Baris

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello,

    But still I can not see the number of the orders. I mean howmany of them spike or none ?

    Thank you

    Baris 

     

  • mariussve1's avatar
    mariussve1
    Solution Sage
    2 years ago

    Hi again,

     

    Can you please check if you have turned off totals in settings for the table?

     

    If not, could you please share a test pbix file with me somewhere?

     

    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

  • 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 🙂