Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Issue with SUMMARIZECOLUMNS

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

CategoryManufacturer Share amount
CAT Amfg 180000
CAT Amfg 270000
CAT Amfg 360000
CAT Bmfg 430000
CAT Bmfg 520000
CAT Bmfg 115000
CAT Cmfg 690000
CAT Cmfg 740000
CAT Cmfg 850000
CAT Cmfg 190000

 

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.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Input Data(Fact table) :- 

     

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

    DateCategoryManufacturer Sales Order Amount
    1/1/2022CAT Amfg 180000
    1/2/2022CAT Amfg 180001
    1/1/2022CAT Amfg 180000
    1/2/2022CAT Amfg 180001
    1/1/2022CAT Amfg 270000
    1/2/2022CAT Amfg 268000
    1/3/2022CAT Amfg 270002
    1/4/2022CAT Amfg 270003
    1/1/2022CAT Amfg 360000
    1/1/2022CAT Bmfg 430000
    1/1/2022CAT Bmfg 520000
    1/1/2022CAT Bmfg 115000
    1/1/2022CAT Cmfg 690000
    1/1/2022CAT Cmfg 740000
    1/1/2022CAT Cmfg 850000
    1/1/2022CAT Cmfg 190000
    1/1/2022BLANK OR NULLmfg 9900000
    1/1/2022BLANK OR NULLmfg 10850000
    1/1/2022CAT DBLANK OR NULL900000
    1/1/2022CAT Dmfg 11000000

     


    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 2278005
    mfg 430000
    mfg 6900000
    mfg 6900000
    BLANK OR NULL (From Cat D) 900000

     

     

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

  • lbendlin's avatar
    lbendlin
    4 years ago

    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])

     

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    "

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Input Data(Fact table) :- 

     

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

    DateCategoryManufacturer Sales Order Amount
    1/1/2022CAT Amfg 180000
    1/2/2022CAT Amfg 180001
    1/1/2022CAT Amfg 180000
    1/2/2022CAT Amfg 180001
    1/1/2022CAT Amfg 270000
    1/2/2022CAT Amfg 268000
    1/3/2022CAT Amfg 270002
    1/4/2022CAT Amfg 270003
    1/1/2022CAT Amfg 360000
    1/1/2022CAT Bmfg 430000
    1/1/2022CAT Bmfg 520000
    1/1/2022CAT Bmfg 115000
    1/1/2022CAT Cmfg 690000
    1/1/2022CAT Cmfg 740000
    1/1/2022CAT Cmfg 850000
    1/1/2022CAT Cmfg 190000
    1/1/2022BLANK OR NULLmfg 9900000
    1/1/2022BLANK OR NULLmfg 10850000
    1/1/2022CAT DBLANK OR NULL900000
    1/1/2022CAT Dmfg 11000000

     


    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 2278005
    mfg 430000
    mfg 6900000
    mfg 6900000
    BLANK OR NULL (From Cat D) 900000

     

     

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

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        thank you for solution 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Above both solutions are working.