cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WilliamPH2 Frequent Visitor
Frequent Visitor

Stop measure from calculating for every item in Power Pivot Chart

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 developementMan Frustrated

 

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/2018Sam1PO1American200.00
01/18/2018Sam2PO2American300.00
02/04/2018Sam3PO3United500.00
02/21/2018Sam4 United40.00
03/10/2018Sam5 American50.00
03/27/2018Sam5PO6Southwest-20.00

 

Sales Table

Sale Date        Name       PO           Vendor            Sale Amount

02/03/18Sam1PO1American400
01/07/18Sam25PO17United600
05/08/18Sam3PO3American1000
05/09/18Sam5PO7Southwest200
03/29/18Sam25PO2American600

 

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

Sam1200000000000000
Sam100000000000000
Sam110000000000000
Sam120000000000000
Sam130000000000000
Sam140000000000000
Sam150000000000000
Sam160000000000000
Sam170000000000000
Sam180000000000000
Sam190000000000000
Sam2300300300300300300300300300300300300300
Sam200000000000000
Sam210000000000000
Sam220000000000000
Sam230000000000000
Sam240000000000000
Sam250000000000000
Sam260000000000000
Sam270000000000000
Sam280000000000000
Sam290000000000000
Sam30500500500000000000
Sam40404040404040404040404040
Sam5003030000000000
Sam60000000000000
Sam70000000000000
Sam80000000000000
Sam90000000000000

 

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

 

Any help is greatly appreciated.

 

William

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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. 

stop_measure

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Super User
Super User

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


WilliamPH2 Frequent Visitor
Frequent Visitor

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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

Community Support Team
Community Support Team

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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 ()
)

When_using_Calculate_in_DAX_Pivot_Chart_displays_all_values_in_otherwise_filtered_P

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WilliamPH2 Frequent Visitor
Frequent Visitor

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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...

Community Support Team
Community Support Team

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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 ()
    )

When_using_Calculate_in_DAX_Pivot_Chart_displays_all_values_in_otherwise_filtered_P2

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WilliamPH2 Frequent Visitor
Frequent Visitor

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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/18Sam1PO1American400
01/07/18Sam25PO17United600
03/29/18Sam25PO2American600
01/03/18Sam3PO10United-700
02/05/18Sam3PO3United1000
05/09/18Sam5PO7Southwest200
02/10/18Sam4PO1American-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.

 

 

Community Support Team
Community Support Team

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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. 

stop_measure

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WilliamPH2 Frequent Visitor
Frequent Visitor

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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 hereMan Frustrated, 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 messMan Sad. 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

 

 

 

 

 

WilliamPH2 Frequent Visitor
Frequent Visitor

Re: When using "Calculate" in DAX, Pivot Chart displays all values in otherwise filtered P

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