Forum Discussion

Shrey03's avatar
Shrey03
Regular Visitor
2 years ago

Summarized table based on dynamic selections

Hi All,

 

Below is my base table -

 

DateColumn 2Column 3Column 4Column 5
2023-12CMF1200
2023-11CMF1200
2023-12EMF1200
2023-11EMF1204835
2023-10EMF1204835
2023-10CMF12761515454
2023-12DMF1256875888
2023-11DMF12761515454
2023-10DMF1256875888
2023-11DAF1206
2023-10DAF1210035
2023-12DAF121006
2023-10CAF125213335117
2023-12CAF127525265659
2023-11CAF126648062601
2023-11EAF127515265624
2023-12EAF126648062595
2023-10EAF125203335111

 

and below is the expected output (Summarized table by Column 2 & 3 unique value and dynamic date selection)

 

DateColumn 2Column 3Column 4Column 5Cal Col1Cal Col2
2023-10 to 2023-12CMF12761515454102.94783900
2023-10 to 2023-12CAF1219386516337715.733048800
2023-10 to 2023-12DMF12189892723043.40824100
2023-10 to 2023-12DAF122004776.5015300
2023-10 to 2023-12EMF12096700.000
2023-10 to 2023-12EAF1219366516333015.663033500
Total Sum  414334379108 7705600

 

Formula for: 

Cal Col1=Abs(Column 5 - Column 4)*100/Column 4

Cal Col2=Cal Col1 * Column 4

 

Can someone please help with a solution to this?

Need Help
Show and Tell

5 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Shrey03 ,

     

    Calculated tables don't respond to slicer selections. They are updated only when the refrenced tables are updated, when the formula is modified or upon data refresh. You can use visuals instead to dynamically change the view and filter that using a slicer but the referenced table must have all the rows needed.

  • Shrey03's avatar
    Shrey03
    Regular Visitor
    2 years ago

    danextian I understand. My objective is to get the Total Sum values (if possible using measures) and not the entire calculated table which I can further use for my caculations

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Shrey03 ,
    I create a table as you mentioned.

    Then I create two calculated columns.

    Cal Col1 =
VAR _B =
    ABS ( 'Table'[Column 5] - 'Table'[Column 4] ) * 100 / 'Table'[Column 4]
RETURN
    IF ( 'Table'[Column 4] = 0, 0, _B )
    Cal Col2 =
'Table'[Cal Col1] * 'Table'[Column 4]

    In the latest version, I think you can manually open the Total value you need.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Shrey03's avatar
      Shrey03
      Regular Visitor
      2 years ago

      Hi Anonymous,

       

      Thank you for the reply. Can you please help using the base table as below 

      to arrive at the highlighted numbers by creating measures as these numbers would be required for further caculations hence having those in Measures would help

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Shrey03 ,

        Are there some columns provided in image 2 or none at all? If none of them are provided, I don't think it can be realized. This is because when New Table is created, there needs to be a link to create the new table.

         

         

        Best Regards

        Yilong Zhou

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.