Home Forums Power Pivot calculated field in subtotal (counter value of currency)

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 8 years, 11 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
• Author
Posts
• #1529

almouchie
Participant
• Started: 1
• Replies: 1
• Total: 2

I am relatively new to power pivot calculated fields & need some help in the sub total calculated field. I am still not very good at DAX formulas.

I have a list of cashiers & transactions executed  (deposits, withdrawals, redemption, refund) per currency (EURO, USD,  GBP).

I want the subtotal of transaction executed to show the sum of the each currency after calculating its counter value in USD.

I also want to show the min & max transactions yet not for each period (year) but for whole period.

I would appreciate any help

P.S My most urgent request is the calculated field in the sub total (highlighted fields in <span style=”text-decoration: underline;”>YELLOW</span>). Attached is demonstration of my data file.

regards,

RoB

Remark: Looking forward to impress my boss 🙂

###### Attachments:
You must be logged in to view attached files.
#1532

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

Rob,

You mention that you are a new to Power Pivot, and the equivalent workbook using Power Pivot would definitely require advanced knowledge.

I recommend starting with something simple (such as single currency in a column).

#1534

almouchie
Participant
• Started: 1
• Replies: 1
• Total: 2

Hello Mr Tomallan

First thanks for your reply. Using a currency per column wouldnt solve my problem. I could do that with a simple pivot.

my main request is writing the dax formula to make the subtotal sum a diferent column (countervalue)

=sum(‘transaction'[c/v])

except that didnt work

is there anything that can give me. Headstart, a similar example, something online i can buy to get quickly acquianted. I am fast learner and will pick up quickly. I taught myself to write codes & change others to fit my request.

i have a deadline & would appreciate any help on that regards

thanks

#1536

tomallan
Keymaster
• Started: 0
• Replies: 417
• Total: 417

Hi,

For a measure (calculated field) that can change to a different formula when creating totals, consider a construct like:

Sum of Amount := IF ( HASONEVALUE ( Table1[CURENCY] ), SUM ( Table1[AMOUNT] ), SUM ( Table1[COUNTER VALUE \$] ) )

—–
The idea behind the formula is that line items have only one currency name, but a total, in your case, would cover more than one currency name.

A problem can arise, though, if someone uses a slicer or filter to drill down into one currency name, then you will get the wrong subtotal.

An interesting blog that discusses HASONEVALUE can be found at:

http://www.powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Although the blog is about suppressing subtotals, and you want to switch to a different formula when subtotaling, the logic and concepts apply.

Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Power Pivot’ is closed to new topics and replies.