Measure with IF function between 2 reports

[Anonymous]

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 

[mariussve1]

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 🙂

[mariussve1]

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]

Hi,

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

Thank you

Baris 

[mariussve1]

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]

Hello,

I have just shared the link with you

Thanks in advance

Baris

[mariussve1]

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]

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]

Hi,

actually I want to count orders which are spikes or none 

Thanks you

Baris 

[mariussve1]

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]

Hi,

it will be great for me. May you send me the updated model ? 🙂

Thank you in advance

Baris

[mariussve1 · Accepted solution]

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]

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 

[Anonymous]

Hello,

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

Thank you

Baris 

[mariussve1]

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]

Hello Marius,

you mean this:

Thanks

Baris

[Anonymous]

Hi

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

Thanks

Baris