Create summary table based on selected filters/slicers

[amalsp_ha]

Hi All,

I am trying to create a visualisation based on the quantity and purchase value with data close to the ones given bellow. But the quantity must be the sum of products purchased by a retailer for the selected period. These products may change on the selected items in the filter. [Meaning, there is a slicer based dropdown that the user can select one or more products, Whereas the summary (table or other) should be created filtered based on that selection on the fly]

Eg. Source Table

Date	InvoiceNo	Retailercode	ProductCode	Quantity	Value
10/29/2020	1000	RTL0001	PROD-001	10	100
10/29/2020	1000	RTL0001	PROD-002	25	300
10/29/2020	1000	RTL0001	PROD-003	7	140
11/2/2020	1001	RTL0002	PROD-002	15	180
11/2/2020	1001	RTL0002	PROD-003	17	340
11/2/2020	1001	RTL0002	PROD-004	5	500
11/3/2020	1002	RTL0003	PROD-002	20	240

Filters applied [from the Slicer based drop down list]

ProducrCode: PROD-002 & PROD-003

Other filters like date and Retailer area also may be selected.

Target Table [Output required]

RetailerCode	Quantity	Value
RTL0001	32	440
RTL0002	32	520
RTL0003	20	240

Target Chart [Output required]

Thanks

Amal

[Syndicate_Admin]

Hello Amal!

In the end you found a solution for this case? I have the same problem and I'm trying to see solutions.
-----------------------------------

Hi Amal!

Did you get a solution for this issue?? I have the same problem and I'm trying to get a solution.

[v-yangliu-msft]

Hi  @amalsp_ha,

Here are the steps you can follow：

1. Create calculated column.

Quantity_new =
CALCULATE(SUM('Table'[Quantity]),ALLEXCEPT('Table','Table'[Date],'Table'[ProductCode]))

Value_new =
CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Date],'Table'[ProductCode]))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amalsp_ha]

Hi Liu Yang,

What I require is the 20 and the 32 in the x axis and the total for 20 and 32 respectively shown by the bar.

Please check the drawing of the bar chart I posted earlier.

Thanks

Amal

[v-yangliu-msft]

Hi  @amalsp_ha,

Here are the steps you can follow：

1. Create measure.

Quantity_new =
CALCULATE(SUM('Table'[Quantity]))

Value_new = 
CALCULATE(SUM('Table'[Value]))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amalsp_ha]

Hi,

Thanks for this, but I need to show this as a chart. The quantity has to go on the x axis and value to the y axis as I have shown in the chart I have given. As far as I know a measure cannot be added to an axis of a chart.

Amal

[v-yangliu-msft]

Hi  @amalsp_ha ,

Here are the steps you can follow：

1. Create a calculated table.

Table 2 =
SUMMARIZE(
'Table','Table'[Retailercode],    
"Quantity",CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[ProductCode]="PROD-002" || 'Table'[ProductCode]="PROD-003")),
"Value",CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[ProductCode]="PROD-002" || 'Table'[ProductCode]="PROD-003")))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amalsp_ha]

Hi Liu Yang,

Thank you for the reply and the solution, but my issue here is that the product codes are not fixed, these are to be selected from a slicer. Also there are other slicers like date, geography. So I cannot use fixed products in the table creation dax. Today it might be PROD-002 & PROD-003 tomorrow it might be PROD-001 & PROD-002.

Hope you have got an idea about my requirment. My apologies if I was not clear in my earlier post.

Amal

[dvnnnaidu]

HI Amal,

Just checking if you have got the solution for your problem. I have exact same scenario to deal with and strugling to fix.