Forum Discussion

hprose's avatar
hprose
Icon for Helper I rankHelper I
3 years ago
Solved

Get accurate matrix totals

Hello,   I have sample data in the below format. For simplicity's sake, I'm only including the customer dimension here, but my original data includes Manager, Director, etc.  I want to create ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi hprose ,

    Please try:

     

    Amount change =
SUMX (
    SUMMARIZE (
        'Sheet1',
        'Sheet1'[Customer],
        "measure",
            CALCULATE ( SUM ( Sheet1[Amount] ), 'Sheet1'[Date] = MAX ( 'Sheet1'[Date] ) )
                - CALCULATE ( SUM ( Sheet1[Amount] ), 'Sheet1'[Date] = MIN ( 'Sheet1'[Date] ) )
    ),
    [measure]
)

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi hprose ,

Please try:

 

Amount change =
SUMX (
    SUMMARIZE (
        'Sheet1',
        'Sheet1'[Customer],
        "measure",
            CALCULATE ( SUM ( Sheet1[Amount] ), 'Sheet1'[Date] = MAX ( 'Sheet1'[Date] ) )
                - CALCULATE ( SUM ( Sheet1[Amount] ), 'Sheet1'[Date] = MIN ( 'Sheet1'[Date] ) )
    ),
    [measure]
)

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

  • hprose's avatar
    hprose
    Icon for Helper I rankHelper I
    3 years ago

    Hello Anonymous , apologies if this needs to go into a different thread. I was wondering how this measure could be modified if I wanted to get the amount difference between the first and last date, irrespective of whether the customer has data for those days.

     

    For example, for A, the amount change should be -5, for B it should be 20, and so on. I was able to achieve this by hardcoding the dates in the Min and Max conditions. Can this is accomplished without hardocing the dates? Thanks again.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi hprose ,

      Yes, please consider opening a new thread next time. Thanks.

      Measure:

      Measure = 
VAR _min_date = CALCULATE(MIN('Sheet1'[Date]),ALL())
VAR _max_date = CALCULATE(MAX('Sheet1'[Date]),ALL())
VAR _result = CALCULATE(SUM('Sheet1'[Amount]),'Sheet1'[Date]=_max_date)-CALCULATE(SUM(Sheet1[Amount]),'Sheet1'[Date]=_min_date)
RETURN
_result

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly -- How to provide sample data