Stop measure from calculating for every item in Power Pivot Chart

[WilliamPH2]

I am new to DAX, and am using it in Power Pivot in Excel.

My problem is (hopefully!) a simple one: I have a measure that actually does what I want it to do, a huge achievement given my slow DAX learning curve. But when I throw it into my pivot chart and add additional fields from my Master Lists into the Rows area, it calculates every single subheading for everysingle item. Because my Master lists are necessarily large, this spits out a pivot table over 1,000,000 lines, which neither Excel nor I can handle....!

This problem is threatening to ruin a beautiful moment in my Dax developement

I know about filtering out zeros from within the Pivot Table, but this is not desirable, since some needed data is zero at times. I need a way to filter so only active PO's, customers, and vendors (ones with data in either the Sales table or the Purchases table) show up. I've messed around, but am driving blind. I don't even know if what I'm asking is possible.

I have a complete sample data set if that is helpful. I show the measure below that.

Purchase Table:

Purchase Date                Name               PO                     Vendor                       Purchase Amount

01/01/2018	Sam1	PO1	American	200.00
01/18/2018	Sam2	PO2	American	300.00
02/04/2018	Sam3	PO3	United	500.00
02/21/2018	Sam4		United	40.00
03/10/2018	Sam5		American	50.00
03/27/2018	Sam5	PO6	Southwest	-20.00

Sales Table

Sale Date        Name       PO           Vendor            Sale Amount

02/03/18	Sam1	PO1	American	400
01/07/18	Sam25	PO17	United	600
05/08/18	Sam3	PO3	American	1000
05/09/18	Sam5	PO7	Southwest	200
03/29/18	Sam25	PO2	American	600

Name Master

Sam1

Sam2

Sam3

Sam4

Sam5

Sam6

Sam7

Sam8

Sam9

Sam10

Sam11

Sam12

Sam13

Sam14

Sam15

Sam16

Sam17

Sam18

Sam19

Sam20

Sam21

Sam22

Sam23

Sam24

Sam25

Sam26

Sam27

Sam28

Sam29

PO Master

PO1

PO2

PO3

PO4

PO5

PO6

PO7

PO8

PO9

PO10

PO11

PO12

PO13

PO14

PO15

PO16

PO17

PO18

PO19

PO20

PO21

PO22

PO23

PO24

PO25

PO26

PO27

PO28

PO29

Vendor Master

American

United

Southwest

Because of other tables in the actual data set, the Master Lists are much larger (as in this sample) than the unique values in the Sales and Purchases tables.

Here is my measure:

Inventory:=CALCULATE
        (IF
            (SUMX(Sales,Sales[Sale Amount])<SUMX(Purchases,[Purchase Amount]),
            SUMX(Purchases,[Purchase Amount])
            ,0),
                   FILTER
                          (ALL('Calendar'[Date]),
                           'Calendar'[Date]<MAX('Calendar'[Date])))

 Like I said, it does what I want. This copy/paste shows how the pivot table looks with just the Name master list. (Both Sales and Purchase tables are linked to all the Master lists).

Inventory     Jan       Feb        Mar       Apr        May      Jun        Jul         Aug       Sep        Oct         Nov       Dec       Grand Total

Sam1	200	0	0	0	0	0	0	0	0	0	0	0	0
Sam10	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam11	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam12	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam13	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam14	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam15	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam16	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam17	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam18	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam19	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam2	300	300	300	300	300	300	300	300	300	300	300	300	300
Sam20	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam21	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam22	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam23	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam24	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam25	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam26	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam27	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam28	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam29	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam3	0	500	500	500	0	0	0	0	0	0	0	0	0
Sam4	0	40	40	40	40	40	40	40	40	40	40	40	40
Sam5	0	0	30	30	0	0	0	0	0	0	0	0	0
Sam6	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam7	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam8	0	0	0	0	0	0	0	0	0	0	0	0	0
Sam9	0	0	0	0	0	0	0	0	0	0	0	0	0

You can imagine how hairy it gets when I add the PO's or vendors.

Any help is greatly appreciated.

William

[Greg_Deckler]

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[WilliamPH2]

Thanks for the advice. I will work on getting that done, although it will be tomorrow before I can get a nice data set together.

William

[v-jiascu-msft]

Hi William,

How about this one?

Inventory 3 =
IF (
    HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] ),
    CALCULATE (
        IF (
            SUM ( Sales[Sale Amount] ) <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    ),
    BLANK ()
)

Best Regards,

Dale

[WilliamPH2]

Dale, thanks so much for taking time to reply to this. I never used HASONEVALUE before. I really like how it cleaned stuff up.

The problem is, the inventory needs to show for each month until a sale is made. E.g., Sam3 needs to show $500 for Feb, Mar, and Apr, and $0 for all the rest.

Any way that can be added to the mix??

Thanks again for taking time for this!

William

The other difficulty with the data is that some PO's apply to more than one customer. I failed to show that in my sample data...

[v-jiascu-msft]

Hi William,

How about this one?

Inventory 2 =
VAR temp =
    CALCULATE (
        IF (
            SUM ( Sales[Sale Amount] ) <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    )
RETURN
    IF (
        HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] )
            || ISBLANK ( temp ) = FALSE (),
        temp,
        BLANK ()
    )

Best Regards,

Dale

[WilliamPH2]

Wow! Thanks so much for giving a working solution. I need to learn more about HASONEVALUE and VAR...

I did have one glitch (not to be ungrateful--it's simply my weird data): if the Sales table looks like this 

Sale Date                                    Name                                   PO                          Vendor                              Sale Amount

02/03/18	Sam1	PO1	American	400
01/07/18	Sam25	PO17	United	600
03/29/18	Sam25	PO2	American	600
01/03/18	Sam3	PO10	United	-700
02/05/18	Sam3	PO3	United	1000
05/09/18	Sam5	PO7	Southwest	200
02/10/18	Sam4	PO1	American	-300

(The order got mixed from the previous sample, but I essentially added a negative to Sam3)

((I also added Sam4, with an identical PO as Sam1, because that happens sometimes))

What happes when you do the Pivot is that Sam3 shows inventory for the rest of the year. Also, the PO subrows under his vendor do not appear. It's very frustrating. (I can't paste my pivot table for some reason to show results).

Would there be a way to filter the calculation first by customer, then by PO? If it did that, where it looked at only the totals for a certain customer and then a certain PO, I think it would work.

It wasn't a huge glitch--I think only one customer out of 300 had the issue. But it would be beyond awesome if it could resolved...

Thanks so much for your patient replies.

William

P.S. If my request is not really feasible, just let me know, and I'll mark your most recent post as a solution, because it really did work in general.

[v-jiascu-msft · Accepted solution]

Hi William,

I attached the pbix file in the attachment. Now we can talk based on the same data.

Please try this one. How should we handle the negative values? It's calculated as 0 in the formula below.

Inventory 3 =
VAR temp =
    CALCULATE (
        IF (
            SUMX ( Sales, IF ( Sales[Sale Amount] < 0, 0, 'Sales'[Sale Amount] ) )
                <= SUM ( Purchases[Purchase Amount] ),
            SUM ( Purchases[Purchase Amount] ),
            0
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MAX ( 'Calendar'[Date] ) )
    )
RETURN
    IF (
        HASONEVALUE ( Purchases[Name] ) || HASONEVALUE ( Sales[Name] )
            || ISBLANK ( temp ) = FALSE (),
        temp,
        BLANK ()
    )

Seems PO doesn't do anything here. Should we consider it?

What could be the influence of the identical PO?

If you can post the expected result, that would make things easier. 

Best Regards,

Dale

[WilliamPH2]

Dale, I just ran the new formula you gave, and it does seem to do the trick!

You've helped me tremendously; I think my real data has a few bugs I'll need to ferret out, but for now I'm very content. Thanks so much for lending your expertise.

Hopefully my first reply today didn't waste too much of your time to read. I thought I should explain what I needed, but I should have tried the new measure you gave first...

If you have comments on anything that could be helpful for my issues here, I'd welcome the input. Otherwise, have a great week, and thanks again!

William

[WilliamPH2]

Thanks so much for sticking with me on this! It's really making my week.

Important change: to see the glitch I'm discussing, in the Sales table you need to change Sam3 (PO3, for $1,000) to a date in Feb, like 2/15 (instead of the current 5/8/18 date) 

The issue comes when you add the PO and Vendor fields to the rows area of  the Pivot Chart. I need that info, because when we scroll down through the chart, we need to be able to see all that at a glance.

As I said, I'm using Excel, and actually just installed Power BI desktop this morning, so I don't know how this works in BI. Can you add rows (like PO and Vendor) and see them as subheadings under customer? That's how the Pivot works in Excel, and it's only when you add the sub-rows of Vendor and PO beneath customer that the issue arises.

I can't figure out how to post screen shots in here, and my pivot table won't copy/paste for some reason. It should look like this:

Sam1

       American

             PO1            200 (Jan)

Sam2

        American

             PO2           300 (Jan-Dec)

Sam3

     In reality, should show no inventory for any month. But my pivot, when I drop in the Vendor and PO fields, shows him as having $500 inventory for Feb-Dec!

Somehow it's combing the negative sale (credit memo) of -$700 from PO10 with the positive $1,000 sale in PO3 and using the net of $300 to show PO3 as still having inventory. In the real data, it seems to be only customers with negative sales (even if the negative sale is for a different PO [for that customer]) that the measure combines it with the current PO's sales and then shows inventory for the purchase because the sale appears to not be there (or be too small).

It is weird, but in the sample, if you change Sam3, (PO3, $1,000) back to a May date, my pivot table measure calculates inventory perfectly. Something about having the sale in the same month as the purchase causes it to add the negative sale from the previous month????

I don't understand all of this, but I believe what I need is for the formula to look first at the customer and then at each unique PO individually. I'd like to run everything off the PO, but some customers have the same PO #, so it has to go "Look at Customer" --> "Look at PO." As it stands now, it does great looking at customers, but if a Customer has two PO's, it seems to combine their totals--only in the case of negative sales.

I know, this is a sort of mess. I haven't had time to try your new measure yet, but I will as soon as I can. I just thought I'd try to respond and explain my situation more thoroughly.

Thank you so much for your help.

William