Forum Discussion

arjun0028's avatar
arjun0028
Regular Visitor
1 year ago
Solved

Calculation of Running Total using quick measure

The line graph displays the running sum of C-Booking on a daily basis. The Currency parameter is created which has values ApTrack, USD and INR. While selecting on the slicer the graph should automatically display the values on the graph. While selecting the field in the slicer no output is being displayed and only getting a straight line.

Code as mentioned below

Running Sum of C-Booking = 
CALCULATE(
    [C-Booking],
    FILTER(
        ALLSELECTED('International EB'[BC_Date].[Day]), 
        'International EB'[BC_Date].[Day] <= MAX('International EB'[BC_Date].[Day])
    )
)

C-Booking = SWITCH(
    TRUE(),
    SELECTEDVALUE('Currency'[Currency]) = "ApTrack", 
        SUMX(
            NATURALINNERJOIN('International EB', 'Rates'), 
            'International EB'[Booking] * 'Rates'[ApTrack] * 1000
        ),
    SELECTEDVALUE('Currency'[Currency]) = "USD", 
        SUMX(
            NATURALINNERJOIN('International EB', 'Rates'), 
            'International EB'[Booking] / 'Rates'[USD]
        ),
    SELECTEDVALUE('Currency'[Currency]) = "INR", 
        SUMX(
            NATURALINNERJOIN('International EB', 'Rates'), 
            'International EB'[Booking] * 1000 / 'Rates'[USD] * 'Rates'[INR] / 100000
        ),
    0
)

 

  • arjun0028 , Try using ALL

    Running Sum of C-Booking =
    CALCULATE(
    [C-Booking],
    FILTER(
    ALL('International EB'[BC_Date].[Day]),
    'International EB'[BC_Date].[Day] <= MAX('International EB'[BC_Date].[Day])
    )
    )

     

     

    and

    dax
    C-Booking =
    SWITCH(
    TRUE(),
    SELECTEDVALUE('Currency'[Currency]) = "ApTrack",
    SUMX(
    NATURALINNERJOIN('International EB', 'Rates'),
    'International EB'[Booking] * 'Rates'[ApTrack] * 1000
    ),
    SELECTEDVALUE('Currency'[Currency]) = "USD",
    SUMX(
    NATURALINNERJOIN('International EB', 'Rates'),
    'International EB'[Booking] / 'Rates'[USD]
    ),
    SELECTEDVALUE('Currency'[Currency]) = "INR",
    SUMX(
    NATURALINNERJOIN('International EB', 'Rates'),
    'International EB'[Booking] * 1000 / 'Rates'[USD] * 'Rates'[INR] / 100000
    ),
    0
    )

5 Replies

  • arjun0028 , Try using ALL

    Running Sum of C-Booking =
    CALCULATE(
    [C-Booking],
    FILTER(
    ALL('International EB'[BC_Date].[Day]),
    'International EB'[BC_Date].[Day] <= MAX('International EB'[BC_Date].[Day])
    )
    )

     

     

    and

    dax
    C-Booking =
    SWITCH(
    TRUE(),
    SELECTEDVALUE('Currency'[Currency]) = "ApTrack",
    SUMX(
    NATURALINNERJOIN('International EB', 'Rates'),
    'International EB'[Booking] * 'Rates'[ApTrack] * 1000
    ),
    SELECTEDVALUE('Currency'[Currency]) = "USD",
    SUMX(
    NATURALINNERJOIN('International EB', 'Rates'),
    'International EB'[Booking] / 'Rates'[USD]
    ),
    SELECTEDVALUE('Currency'[Currency]) = "INR",
    SUMX(
    NATURALINNERJOIN('International EB', 'Rates'),
    'International EB'[Booking] * 1000 / 'Rates'[USD] * 'Rates'[INR] / 100000
    ),
    0
    )

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi arjun0028,

    Thanks for reaching out to the Microsoft fabric community forum.

    I would also take a moment to thank bhanu_gautam for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

    If using ALL instead of ALLSELECTED in the running sum calculation ensures that the graph updates correctly based on the currency selection. This change removes unwanted filters from the date column, which was causing the straight line issue.

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Best Regards,
    Harshitha.
    Community Support Team

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi arjun0028,

      Just wanted to check if you had the opportunity to review the suggestion provided?

      If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

       

      Thank You.

      Harshitha.

      Community Support Team.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi arjun0028 ,

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.


        Thank you.