Value sum from Lower level-High

[sprao]

Hi Experts,

Could you please help me below scnario..i am struggling to get solution..This calcultion with out any Dim table or Copy Table need solution.

Graph 1 need sum values for level 1(Actvity) along with level 3 information also sum based on Refer Actvity.

Graph 2 only Level 3 information

Please help me....

[v-alq-msft · Accepted solution]

Hi, @sprao 

In the ‘Fields’ pane, you can create two measure. Here they are.

SumActual = CALCULATE(SUM('Table B'[Actual Sales]),FILTER(ALL('Table B'),[Actvitity] in FILTERS('Table A'[Actvitity]) || [Actvitity] in SELECTCOLUMNS(FILTER(ALL('Table A'),[ReferA Activity] in FILTERS('Table A'[Actvitity])),"Refer",[Actvitity])))

SumTarget = CALCULATE(SUM('Table A'[Target Sales]),FILTER(ALL('Table A'),[Actvitity] in FILTERS('Table A'[Actvitity]) || [ReferA Activity] in FILTERS('Table A'[Actvitity])))

Then you can create clustered column charts with measures dragged to the visual level filter pane.

Result:

  

If I misunderstand the your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

Best Regards,

Allan

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