Issue with SUMMARIZECOLUMNS

[Anonymous]

Hi All,

Thank you in advance for reading this post.  (this is reposted)

My intension here is to find mfg who are all competiors to mfg 1

Case 1: Cat A has mfg 1 higest share amount, but i need to return mfg 2 since he has next highest value

Case 2: Cat C has mfg1 and mfg 6 has same share amount but i need to return mf6 here since it is competitor 

Case 3: Cat B I have to return Mfg 4 since in that category it has grater share amount.

Case 4: Category might be blank/mfg might be blank i need to display mfg other than mfg1

Step 1:-

The below table is coming from SUMMARIZECOLUMNS

Category	Manufacturer	Share amount
CAT A	mfg 1	80000
CAT A	mfg 2	70000
CAT A	mfg 3	60000
CAT B	mfg 4	30000
CAT B	mfg 5	20000
CAT B	mfg 1	15000
CAT C	mfg 6	90000
CAT C	mfg 7	40000
CAT C	mfg 8	50000
CAT C	mfg 1	90000

DAX:- 

Top Competitor1 =

VAR _CategoryManfucaturer =

SUMMARIZECOLUMNS (

Categories[Category],

Manufacturers[Manufacturer],

"Share", [YTD Share Amount],  <--- Year to Date share amount aggregated measure (not from table )

)

VAR _Groupby =

GROUPBY (

_CategoryManfucaturer,

Categories[Category],

Manufacturers[manufacturer],

"Share", MAXX ( CURRENTGROUP (), [Share] )

)

RETURN

_Groupby

Step 2:- 

Using this solution (Click here to check previous post ) On top of above table resolved my issue partially. If i have table with aggregated values like above.

Here are my challeges:- 

1) Summarizecolumns is not responding for filterchanges.  

2) Once summarizecolumns calculated the table, table is fixed. we can not feed data dynamically.

3) My model lookup/dimension & facts are another data model. I am using live connections to the data sets.

Here are the things i have:- 

Categories[Category],   

Manufacturers[Manufacturer],

"Share", [YTD Share Amount], aggregated measure

Here is what i am looking for a table visual to satisfy below condtinons from above data and above data should not be from summarizecolumns  table

My intension here is to find mfg who are all competiors to mfg 1

Case 1: Cat A has mfg 1 higest share amount, but i need to return mfg 2 since he has next highest value

Case 2: Cat C has mfg1 and mfg 6 has same share amount but i need to return mf6 here since it is competitor 

Case 3: Cat B I have to return Mfg 4 since in that category it has grater share amount.

Case 4: Category might be blank/mfg might be blank i need to display mfg other than mfg1.

Thanks for reading post.

Ravi.

[Anonymous · Accepted solution]

Input Data(Fact table) :- 

My intension here is to find mfg who are all competiors to mfg 1

Date	Category	Manufacturer	Sales Order Amount
1/1/2022	CAT A	mfg 1	80000
1/2/2022	CAT A	mfg 1	80001
1/1/2022	CAT A	mfg 1	80000
1/2/2022	CAT A	mfg 1	80001
1/1/2022	CAT A	mfg 2	70000
1/2/2022	CAT A	mfg 2	68000
1/3/2022	CAT A	mfg 2	70002
1/4/2022	CAT A	mfg 2	70003
1/1/2022	CAT A	mfg 3	60000
1/1/2022	CAT B	mfg 4	30000
1/1/2022	CAT B	mfg 5	20000
1/1/2022	CAT B	mfg 1	15000
1/1/2022	CAT C	mfg 6	90000
1/1/2022	CAT C	mfg 7	40000
1/1/2022	CAT C	mfg 8	50000
1/1/2022	CAT C	mfg 1	90000
1/1/2022	BLANK OR NULL	mfg 9	900000
1/1/2022	BLANK OR NULL	mfg 10	850000
1/1/2022	CAT D	BLANK OR NULL	900000
1/1/2022	CAT D	mfg 1	1000000

Lookup table (Dimensions)

Manfacturer

mfg 1

mfg 2

mfg 4

mfg 5

mfg 6

mfg 7

mfg 8

mfg 9

Lookup table 2

Category

CAT A

CAT B

CAT C

CAT D

Share Amount Measure
Share Amount=	sum(Sales Order Amount)

Output visual expected:-

	share amount
mfg 2	278005
mfg 4	30000
mfg 6	900000
mfg 6	900000
BLANK OR NULL (From Cat D)	900000

I am not sure how to attach excel here so i pasted data in tables

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Above both solutions are working.

[Anonymous · Accepted solution]

Input Data(Fact table) :- 

My intension here is to find mfg who are all competiors to mfg 1

Date	Category	Manufacturer	Sales Order Amount
1/1/2022	CAT A	mfg 1	80000
1/2/2022	CAT A	mfg 1	80001
1/1/2022	CAT A	mfg 1	80000
1/2/2022	CAT A	mfg 1	80001
1/1/2022	CAT A	mfg 2	70000
1/2/2022	CAT A	mfg 2	68000
1/3/2022	CAT A	mfg 2	70002
1/4/2022	CAT A	mfg 2	70003
1/1/2022	CAT A	mfg 3	60000
1/1/2022	CAT B	mfg 4	30000
1/1/2022	CAT B	mfg 5	20000
1/1/2022	CAT B	mfg 1	15000
1/1/2022	CAT C	mfg 6	90000
1/1/2022	CAT C	mfg 7	40000
1/1/2022	CAT C	mfg 8	50000
1/1/2022	CAT C	mfg 1	90000
1/1/2022	BLANK OR NULL	mfg 9	900000
1/1/2022	BLANK OR NULL	mfg 10	850000
1/1/2022	CAT D	BLANK OR NULL	900000
1/1/2022	CAT D	mfg 1	1000000

Lookup table (Dimensions)

Manfacturer

mfg 1

mfg 2

mfg 4

mfg 5

mfg 6

mfg 7

mfg 8

mfg 9

Lookup table 2

Category

CAT A

CAT B

CAT C

CAT D

Share Amount Measure
Share Amount=	sum(Sales Order Amount)

Output visual expected:-

	share amount
mfg 2	278005
mfg 4	30000
mfg 6	900000
mfg 6	900000
BLANK OR NULL (From Cat D)	900000

I am not sure how to attach excel here so i pasted data in tables

[lbendlin · Accepted solution]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

"intension here is to find mfg who are all competiors to mfg 1"  - Wouldn't that just be a list of manufacturers?  Your expected output doesn't seem to match the description.

Step 1:  Identify if the category is covered by Mfg 1:

HasMfg1 := if(calculate(countrows('Input Data'),'Input Data'[Manufacturer ]="mfg 1")>0,1,0)

Step 2: Identify the largest manufacturer by category that is not Mfg 1

MaxComp := 
var a = CALCULATETABLE('Input Data','Input Data'[Manufacturer ]<>"mfg 1")
var b = SUMMARIZE(a,'Input Data'[Manufacturer ],"amt",sum([Sales Order Amount]))
var c = TOPN(1,b,[amt],DESC)
return CONCATENATEX(c,[Manufacturer ])

Step 3: do the same for the value

MaxCompValue := 
var a = CALCULATETABLE('Input Data','Input Data'[Manufacturer ]<>"mfg 1")
var b = SUMMARIZE(a,'Input Data'[Manufacturer ],"amt",sum([Sales Order Amount]))
var c = TOPN(1,b,[amt],DESC)
return CONCATENATEX(c,[amt])

[Anonymous]

thank you for solution 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

Share the raw data tables in a format the can be pasted in an MS Excel file.

[lbendlin]

"

1) Summarizecolumns is not responding for filterchanges.  

2) Once summarizecolumns calculated the table, table is fixed. we can not feed data dynamically.

"

That is standard behavior for calculated columns and calculated tables.  You would need to use measures to get the desired behavior.

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.