Measure won't slice properly

[japie06]

Hi there!

I am a graduate intern at a big company and I'm having some trouble with creating a measure in PowerPivot.
I'm quite new with PowerPivot and I need some help. I am the first person to use PowerPivot in this office so I can't ask for help here.

I have a fact table that has basically all journal entries. See next table. All entries are done with a unique ID (serialnumber) for every product

ID	DATE	ACCOUNT#	AMOUNT
110	2010-1-1	900	$1000

<tbody>
</tbody>

There is a dimension table with has all accounts allocated to a specific country and expense or revenue.

ACCOUNT#	Expense	Country
900	Revenue	Germany

<tbody>
</tbody>

And another dimension table to split the dates.
The third dimension table contains product information, but also contains a column with a certain expense (Expense X).

ID	Expense X	ProductName	Productcolour
110	$50	Flower	Green

<tbody>
</tbody>

I made sure I made the correct relations between the tables of course. And slicing works in general.

To calculate the margin I need to deduct this expense x from the revenue. I already made a measure that shows total Revenue, that one was easy.

Now I need a measure to show the total for Expense X, related to productID. So I can slice in a pivot table on date and product name etc.

The problem is that I can't use RELATED function because the serial number is used multiple times in the fact table (journal entries can have the same serial number)
And if I use the SUM or CALCULATE function it won't slice properly.

So how can I calculate the total for expense X so it will slice properly?

 

[anvg]

Hi
Could you upload a little example into anywhere (maybe dropbox or other)?
Regards,

 

[japie06]

Hi thanks for replying.

I uploaded a dummy database here

So I need to calculate the sum of Expense X in relation to the unique serialnumber. I need to be able to slice by month for example.

 

[anvg]

Hi
If I understand you right a needed measure is

Total Expense:=CALCULATE(SUM('ProductTable'[Expense]), CALCULATETABLE('FactTable','ExpenseTable'[Expenses]="Revenue"))

Sorry, I can upload an example file for you but it is a problem - I have Excel 2016 Power Pivot but you have 2010. You cannot open a measure edit dialog.
Regards, Andrey.

 

[anvg]

If you need with calculate total expense with count of SerialNumber in FactTable it is

CALCULATE(SUMX('ProductTable', 'ProductTable'[Expense]  * COUNTROWS(RELATEDTABLE('FactTable'))), 
CALCULATETABLE('FactTable','ExpenseTable'[Expenses]="Revenue"))

Good luck.

 

[japie06]

Thank you very much, that was exactly what we needed!

 

[japie06]

If you need with calculate total expense with count of SerialNumber in FactTable it is

CALCULATE(SUMX('ProductTable', 'ProductTable'[Expense]  * COUNTROWS(RELATEDTABLE('FactTable'))), 
CALCULATETABLE('FactTable','ExpenseTable'[Expenses]="Revenue"))

Good luck.

Hey I have noticed that this forumula doesn't quite work. The reason is that in the fact table there can be several journal entries for revenue. This means that it will count the total expense twice for that specific serial number.

Is there a way to maybe only count unique values instead of count?

 

[anvg]

Hi
Try with my Zippyshare.com - dummy table.xlsx example of dictinct count
Regards,