calculated field in subtotal (counter value of currency)

[almouchieParticipant Started: 1Replies: 1Total: 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.

[tomallanKeymaster Started: 0Replies: 417Total: 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).

[almouchieParticipant Started: 1Replies: 1Total: 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

 

[tomallanKeymaster Started: 0Replies: 417Total: 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.

[Author]

Posts