cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Incorrect totals when using % calculations with quantity and filters

Hello,

I am trying to calculate PY Sales variance and each time I bring in the calculation for PY the totals do not add up. Here are all my formulas in order:

QTY PY =
IF (
ISCROSSFILTERED( Sheet1[INVOICE DATE].[Year]),
CALCULATE( sum(Sheet1[QTY SOLD]),
BLANK())

PY Total List Price =
IF (
ISCROSSFILTERED( Sheet1[INVOICE DATE].[Year]),
CALCULATE( sum(Sheet1[TOTAL LIST PRICE]),
BLANK())

PY Discount \$ = sum(Sheet1[NET SALES])-[PY Total List Price]
PY Avg List Price/Ut = [PY Total List Price]/sum(Sheet1[QTY SOLD])
PY Discount % = ([PY Discount \$]/[PY Total List Price])
Sales Discount Var PY = (sum(Sheet1[QTY SOLD])*[PY Discount %]) - this gives me incorrect totals. If I change PY Total List Price and PY QTY and remove filter dateadd, the totals will be correct.
How to change the calculatiosn to give me the right totals?
Thank you!

3 REPLIES 3
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

Multiplication should happen at line level or some group level.

So we we do Sum(A*B) Not sum(a)* sum(b)

Do do this is to take a row context

sumx(summarize(table,table[Date],"_sum",sum(Sheet1[QTY SOLD]),"_dis",[PY Discount %]),[_sum]*[_dis])

table[date] is the row context where calculation need to done, it can be id or month as per need

Regular Visitor

Hi,

test = sumx(summarize(Sheet1,Sheet1[INVOICE DATE].[Date],"_sum",sum(Sheet1[QTY SOLD]),"_dis",[PY Discount %]),[_sum]*[_dis])

but the result was infinity

Am I doing something wrong with calculations?
Thank you

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors