• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Blanks in Power Pivot table

aggie81

Member
I use Power Query to transform data from Excel files into appends. There are two results that are loaded into the data model and a relationship is created between them. The data model is then loaded to a Power Pivot table.
It works well enough but on the Description field on the pivot table, there are blanks that must be filtered out by unchecking them.
The AppOrders are units to be produce for May 9, 2022, in this instance.
The SpOrders2022 has customer orders.
In my mind, the pivot table should only pull in the SpOrders2022 items that match in AppOrders but there are blanks. It may be that the blanks are orphans in the SpOrders2022 with no match in AppOrders table and filter is the easiest way to not show them????
I have attached a workbook and hope it helps.
I manage a production greenhouse and use this to know and tell production crew what orders to target when a planting list is generated.
Thank you for reading and look forward to your thoughts.
Lee
 

Attachments

Chihiro

Excel Ninja
It would help if you upload workbook with pivot table or at least of what your current pivot fields looks like.

Also, make sure to have your data model populated. When I took a look at your workbook. It's missing the model.

As for your question. There are few ways to handle it.
1. Using DAX measure to control what is returned in pivot to remove blanks. Ex: IF(HASONEVALUE(),Calc.Alternate Calc) etc.
2. Using table variable to construct special context for DAX measure.
3. If using PowerBI, you can use calculated table (you can't use it in Excel's data model).

etc.
 

Chihiro

Excel Ninja
Replace your 2 value field columns with following DAX measures in your case.

Code:
UnitBooked :=
IF (
    DISTINCTCOUNT ( AppOrders[Crop #] ) = 1,
    SUM ( SPOrders2022[Units Booked] ),
    BLANK ()
)
Code:
ProductionQTY :=
IF (
    DISTINCTCOUNT ( AppOrders[Crop #] ) = 1,
    SUM ( SPOrders2022[Production QTY] ),
    BLANK ()
)
 

Chihiro

Excel Ninja
Oh, by the way. Above is inflexible method and if you change pivot structure, it's going to return unexpected result.
 

Chihiro

Excel Ninja
If you need it to be more dynamic and also calculate Grand Total. You need to pass filter context using CALCULATE.
Ex:
Code:
UnitBooked :=
VAR distCount =
    CALCULATE ( DISTINCTCOUNT ( AppOrders[Crop #] ), SPOrders2022 )
RETURN
    IF (
        distCount >= 1,
        CALCULATE ( SUM ( SPOrders2022[Units Booked] ), AppOrders ),
        BLANK ()
    )
 

aggie81

Member
Thanks for adding the totals, I was in the process of asking about it when I saw your message.
I halfway understand the code you wrote but not fully. It is working well.
I will create a new file next week and hope there aren't any problems.
 

aggie81

Member
Thank you for sharing the link. Leila Gharani is able to take something complicated and explain it in simple terms and examples.
All is working well.
 
Top