Forum Discussion

shareezsaleem's avatar
shareezsaleem
Helper III
4 years ago
Solved

Convert data in to dynamic summarized data

Hi All,

 

Assume, I have table with below like below in Power BI.

DateParticulars Value Year

1-Jan-2021Beginning_# Contracts9822021
1-Jan-2021Expired_# Contracts-972021
1-Jan-2021New_# Contracts1322021
1-Jan-2021Total Active_# Contracts10172021
1-Feb-2021Beginning_# Contracts10172021
1-Feb-2021Expired_# Contracts-942021
1-Feb-2021New_# Contracts742021
1-Feb-2021Total Active_# Contracts9972021

 

I have 2 slicers, Month & Year (Calendar Table connected to the above table)

I need to have a summarized table (dynamic) based on the selection made on the slicers.

If I select Year 2021 and month Jan & Feb, I need to see the data like below:

Beginning_# Contracts982
Expired_# Contracts-191
New_# Contracts206
Total Active_# Contracts997


This is for creating Waterfall Chart

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, shareezsaleem ;

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? In addition, you also could create a measure as follow:

    newvalue = 
SWITCH(MAX('Table'[Attribute]),
            "Beginning_# Contracts",CALCULATE(SUM('Table'[Value]),FILTER('Table',EOMONTH([Period],0)=EOMONTH(MIN('Date'[Period]),0))),
            "Total Active_# Contracts",CALCULATE(SUM('Table'[Value]),FILTER('Table',EOMONTH([Period],0)=EOMONTH(MAX('Date'[Period]),0)))
            ,SUM([Value]))

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, shareezsaleem ;

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? In addition, you also could create a measure as follow:

    newvalue = 
SWITCH(MAX('Table'[Attribute]),
            "Beginning_# Contracts",CALCULATE(SUM('Table'[Value]),FILTER('Table',EOMONTH([Period],0)=EOMONTH(MIN('Date'[Period]),0))),
            "Total Active_# Contracts",CALCULATE(SUM('Table'[Value]),FILTER('Table',EOMONTH([Period],0)=EOMONTH(MAX('Date'[Period]),0)))
            ,SUM([Value]))

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    If you are talking about summary table calculated with DAX to get affected by slicers. Then you need to create a relationship between new summarized table and the calender table based on date column. Once relationship is created, you can filter the data based on slicers.

  • Thejeswar's avatar
    Thejeswar
    Super User
    4 years ago

    Hi shareezsaleem ,

    Not sure what your requirement here is.

     

    From the expected output I see, two particulars are summing up Jan and Feb values while the other values show the Jan values alone

     

    You can just get this without even building a summary table. Below is the screenshot

     

    Regards,

     

    • shareezsaleem's avatar
      shareezsaleem
      Helper III
      4 years ago

      I need to create a waterfall chart from the below and visual should change based on the Year and Month Slicers. If I select 2021 and month as Mar, Apr, May- it should show be the contract at the begining as the March's number, Contract expired & new contracts as the sum of values of above 3 months and Total Active contracts as the sum of these 3.

      PeriodAttribute Value Year

      1-Jan-21Beginning_# Contracts9822021
      1-Jan-21Expired_# Contracts-972021
      1-Jan-21New_# Contracts1322021
      1-Jan-21Total Active_# Contracts10172021
      1-Feb-21Beginning_# Contracts10172021
      1-Feb-21Expired_# Contracts-942021
      1-Feb-21New_# Contracts742021
      1-Feb-21Total Active_# Contracts9972021
      1-Mar-21Beginning_# Contracts9972021
      1-Mar-21Expired_# Contracts-892021
      1-Mar-21New_# Contracts1012021
      1-Mar-21Total Active_# Contracts10092021
      1-Apr-21Beginning_# Contracts10092021
      1-Apr-21Expired_# Contracts-632021
      1-Apr-21New_# Contracts832021
      1-Apr-21Total Active_# Contracts10292021
      1-May-21Beginning_# Contracts10292021
      1-May-21Expired_# Contracts-762021
      1-May-21New_# Contracts702021
      1-May-21Total Active_# Contracts10232021



      Expected result:

      Beginning_# Contracts997
      Expired_# Contracts-228
      New_# Contracts254
      Total Active_# Contracts1023

       

       

       

      • Thejeswar's avatar
        Thejeswar
        Super User
        4 years ago

        Hi shareezsaleem ,

        Can you try with the below DAX. This works fine as per my testing

         

        newvalue = 
IF(MAX('Table'[Particulars]) = "Beginning_# Contracts",
VAR temptable = TOPN(1,
                    ADDCOLUMNS('Table', "@difference", MONTH('Table'[Date]) - SELECTEDVALUE('Date'[Month])), [@difference], ASC)
return
    MINX(temptable, 'Table'[Value]),
    IF(MAX('Table'[Particulars]) = "Total Active_# Contracts",
VAR temptable = TOPN(1,
                    ADDCOLUMNS('Table', "@difference", MONTH('Table'[Date]) - SELECTEDVALUE('Date'[Month])), [@difference], DESC)
return
    MAXX(temptable, 'Table'[Value]),
    SUM('Table'[Value])))

         

        Below is the screenshot

         

        If this solves your requirement, mark it as solution!! Appreciate with your Kudos!!

        Regards,

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yeah Thejeswar, I agree. The need can be achieved by simple drag and drop. Because as we drop in the "Values" field of visualization, Power BI automatically sums/aggregate the value column by creating implicit measures.