Forum Discussion
Power Query - how to exclude members where measure is blank?
Hi Community, I hope you are doing very good.
I have a data model in Excel trough Power Query where I have the Sales table and the Budget table. Also, I've created a master table for products and other for customers.
These two master tables have one-to-multiple relationships
- Products table is linked with both Sales and Budget tables.
- Customer is linked only with Sales table, because I have general budget that is not at customer level.
I've created two measures for each table (sales and budget) for adding quantity sales: sales quantity and budget quantity.
What I need in my pivot is to open each product to see the customers that placed orders for each one, but I don't want that those customers that don't have orders would be showed there. But I couldn't do that. When I expand the customers for each product, it shows all customers, even if these don't have sales quantity.
This the current output of the pivot table:
This is the wanted output that I can't achieve:
Is it possible? Could you help me giving your advice, please?
Thank you very much.
Regards.
Ale
For such model we could use two measures:
Order quantity:=SUM ( Orders[Orders] ) Budget quantity:=CALCULATE ( SUM ( Budget[Budget] ), TREATAS ( VALUES ( Orders[Product] ), Budget[Product] ) )Result is
6 Replies
With
measure could be
Budget Quantity:=CALCULATE( SUM ( Budget[Budget] ), TREATAS( VALUES( Orders[Customer] ), Budget[Customer] ) )Hi SergeiBaklan !!
I appreciate so much your help and time. Thank you very much!. My data model is a bit different. What happens is that I don't have budget at customer level. For that reason I agree that the pivot show the budget quantity as drill drown showing for each customer the total budget for the product. However, I don't want that the pivot show every customer when they don't have sales.
My data model:
My expectation is that the pivot show as the following, but I couldn't make it.
Thank you again.
Regards
Ale
Adding the relationships:
