Seriously struggling measuring consumption of purchased products

[cammj]

Hi,

I've been going for hours on this one and i cant seem to get this right.

I have two tables,

Table 1 - call it ProductPurchases - shows when a company purchased a product and how much of it

E.g

Company,Product,Quantity
CompanyA,ProductA,50
CompanyA,ProductA,20
CompanyA,ProductB,10

Table 2 - call it ProductUsage - shows when that product was used

Staff Member,Company,Product
Joe,CompanyA,ProductA
Sam,CompanyA,ProductB

I want to show a table showing how much of each product is still available

I have created a column in ProductPurchases called Consumed with DAX

Consumed = CALCULATE(

COUNTROWS (ProductUsage),

FILTER (ProductUsage,

ProductUsage[Product] = ProductPurchases[Product] &&

ProductUsage[Company] = ProductPurchases[Company]

))

When put in to a table this correctly shows how much of the product by the company is consumed

The issue is when i want to do a percentage of consumed, i use the following DAX
PctConsumed = (ProductPurchases[Consumed]/ProductPurchases[Quantity])*100

The PctConsumed never displays correctly. Any ideas on how i can make this work?

[kentyler]

One of the problems learning DAX is that any measure is also influenced by a "filter context" generated by all the visuals on a report.

So your measure

PctConsumed = (ProductPurchases[Consumed]/ProductPurchases[Quantity])*100

probably productpurchases is being also filtered by a visual that is displaying a particular row.

To change the external filter context for a measure you use the ALL() function inside of a CALCULATE() function... so something like 

PctConsumed =CALCULATE( (ProductPurchases[Consumed]/ProductPurchases[Quantity])*100, ALL(ProductPurchases))

might work.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.