Clustered Bar chart with out aggreation.

[bideveloper555]

hi

this is the second request.i feel its bit diffcult to figure this out.

first table is kind of customer info, john has 3 properties and 2 of them have fixed fees.(12 months)

second table,transcation of each of his property.

i am trying to show a bar chart,fixed fee vs sum of trans amount.in below i gave only 3 months but actually can have years of data.

lets say, i need to show john first property(r_ID = 12)

bar chart should show 5000 vs 9000 which i can do on table chart but i cant  on bar chart,as table chart has dont summarize.

	T_ID	r_ID	name	fee
	1	12	john	5000
	2	45	john	4000
	3	12	john
	ID	T_ID	period	Amt
	4	1	jan	2000
	5	1	feb	3000
	6	1	mar	4000
	7	2	jan	6000
	8	2	feb	2000
	9	2	mar	3000
	period	T_ID	name	fee	amt
	for 3 months	1	John	5000	9000
	for 3 months	2	john	4000	11000

[v-yingjl · Accepted solution]

Hi @bideveloper555 ,

Since you may have a whole year of data for each T_ID and your month column is a text type instead of a date column, assuming you sort the month of each T_ID by the size of each ID, the sum of 3 months ago should be from every The sum of the 3 rows before the maximum ID of a T_ID, you can create the following measures:

previous 3 months Fee = 
CALCULATE (
    SUM ( Fee_table[fee] ),
    FILTER (
        ALL ( Fee_table ),
        'Fee_table'[T_ID] IN DISTINCT ( 'Amt_Table'[T_ID] )
    )
)

previous 3 months Amt = 
CALCULATE (
    SUM ( Amt_Table[Amt] ),
    FILTER (
        ALL ( Amt_Table ),
        'Amt_Table'[T_ID]
            IN DISTINCT ( Fee_table[T_ID] )
                && 'Amt_Table'[ID]
                    <= CALCULATE (
                        MAX ( 'Amt_Table'[ID] ),
                        FILTER ( Amt_Table, 'Amt_Table'[T_ID] = EARLIER ( Amt_Table[T_ID] ) )
                    )
                && 'Amt_Table'[ID]
                    >= CALCULATE (
                        MIN ( 'Amt_Table'[ID] ),
                        FILTER ( Amt_Table, 'Amt_Table'[T_ID] = EARLIER ( Amt_Table[T_ID] ) )
                    ) - 2
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@bideveloper555 see if attached is what you are looking for

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡