CODEWITHSUNDEEP
filterdaxpowerpivotsumifs
Anthony S
Anthony S

Reputation: 1

Sumifs Ratio in PowerPivot

I have a table in Excel, utilizing Power Pivot that I then display/filter using a Pivot Table. Within my dataset, calculating a ratio within Power Pivot that "sums" correctly in the pivot table based on slicers is fine - this utilizes a SUMX(Cost)/SUMX(Total) and everything works fine. By sums correctly, I mean if I further break down the data set based on Region/State/Product/Employee, all those Rows sum up correctly for the ratio percentage.

The dataset is filtered based on a single month or range of months. The result of this works fine for either the single month or range of Months. What I'm trying to do is within my Pivot Table, show a current month ratio AND a year to date ratio. I've tried messing around with equations I've found online, but nothing seems to work. This includes the following attempts:

=CALCULATE([Cost],[ProductID]="224594")/CALCULATE([Total],[ProductID]="224594")

=SUMX (FILTER(ALL('TableName'),PATHCONTAINS ('TableName'[ProductID], EARLIER('TableName'[ProductID]))),'TableName'[Cost]) / SUMX(FILTER(ALL('TableName'),PATHCONTAINS('TableName'[ProductID], EARLIER ('TableName'[ProductID]))),'TableName'[Total])

I need the "sumifs" to sum the cost for Product A for all months divided by the sum of total for Product A for all months. I do not want to hard code in the the Product ID into the equation, but simply sum all previous records for that product, but I can't seem to get this to work.

Any suggestions?

Sample Data Set

Upvotes: 0

Views: 163

Answers (1)

Anthony S
Anthony S

Reputation: 1

I used the calculate and filter functions in a column instead of trying to use them in a measure, which fixed the problem.

Upvotes: 0

Related Questions