Convert data in to dynamic summarized data

[shareezsaleem]

Hi All,

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

DateParticulars Value Year

1-Jan-2021	Beginning_# Contracts	982	2021
1-Jan-2021	Expired_# Contracts	-97	2021
1-Jan-2021	New_# Contracts	132	2021
1-Jan-2021	Total Active_# Contracts	1017	2021
1-Feb-2021	Beginning_# Contracts	1017	2021
1-Feb-2021	Expired_# Contracts	-94	2021
1-Feb-2021	New_# Contracts	74	2021
1-Feb-2021	Total Active_# Contracts	997	2021

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_# Contracts	982
Expired_# Contracts	-191
New_# Contracts	206
Total Active_# Contracts	997

This is for creating Waterfall Chart

[v-yalanwu-msft · Accepted solution]

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.

[Thejeswar]

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,

[tej147]

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.

[shareezsaleem]

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-21	Beginning_# Contracts	982	2021
1-Jan-21	Expired_# Contracts	-97	2021
1-Jan-21	New_# Contracts	132	2021
1-Jan-21	Total Active_# Contracts	1017	2021
1-Feb-21	Beginning_# Contracts	1017	2021
1-Feb-21	Expired_# Contracts	-94	2021
1-Feb-21	New_# Contracts	74	2021
1-Feb-21	Total Active_# Contracts	997	2021
1-Mar-21	Beginning_# Contracts	997	2021
1-Mar-21	Expired_# Contracts	-89	2021
1-Mar-21	New_# Contracts	101	2021
1-Mar-21	Total Active_# Contracts	1009	2021
1-Apr-21	Beginning_# Contracts	1009	2021
1-Apr-21	Expired_# Contracts	-63	2021
1-Apr-21	New_# Contracts	83	2021
1-Apr-21	Total Active_# Contracts	1029	2021
1-May-21	Beginning_# Contracts	1029	2021
1-May-21	Expired_# Contracts	-76	2021
1-May-21	New_# Contracts	70	2021
1-May-21	Total Active_# Contracts	1023	2021

Expected result:

Beginning_# Contracts	997
Expired_# Contracts	-228
New_# Contracts	254
Total Active_# Contracts	1023

[Thejeswar]

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,

[tej147]

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.