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 4 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.