Forum Discussion

Raymo3u99's avatar
Raymo3u99
Icon for Helper I rankHelper I
6 years ago
Solved

How can I create a filtered table

I have a table with the following data.

 

PathSegmentCriteriaValue
Northbound190FDefault276000
Northbound190FIncrease132300
Northbound210BDefault286000
Northbound210BIncrease132300
Northbound220BDefault95000
Northbound220BIncrease75000
Northbound230BDefault95000
Northbound230BIncrease75000
Northbound240BDefault95000
Northbound240BIncrease75000
Northbound250BDefault95000
Northbound250BIncrease75000
Northbound260BDefault95000
Northbound260BIncrease75000
Northbound300BDefault95000
Northbound300BIncrease75000
Northbound310BDefault95000
Northbound310BIncrease75000
Southbound190BDefault231800
Southbound190BIncrease109200
Southbound210FDefault237350
Southbound210FIncrease110700
Southbound220FDefault235350
Southbound220FIncrease110100
Southbound230FDefault262000
Southbound230FIncrease110100
Southbound240FDefault261000
Southbound240FIncrease109800
Southbound250FDefault259000
Southbound250FIncrease109200
Southbound260FDefault258000
Southbound260FIncrease108900
Southbound300FDefault306000
Southbound300FIncrease123300
Southbound310FDefault306000
Southbound310FIncrease123300

 

I need to display the path and the minimum sum ( value) of all segments within the path. I also want to be able to create a slicer on the Criteria column.

 

So if both Default and Increase are selected in the criteria slicer, my output should be:

PathMin Value
Northbound170000
Southbound341000

 

If just Default is selected in the criteria slicer my output should be:

PathMin Value
Northbound75000
Southbound231800

 

Thanks for your help.

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Raymo3u99 ,

     

    could you give an overview over your model?

    I think you only have to replace the data[path] with the pathitems.

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

     

5 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi Raymo3u99 

     

    Try this:

     

    Min Value = MINX(SUMMARIZE(data;data[Path];data[Segment]; "Sum of Value"; SUM(data[Value])); [Sum of Value])
     
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.
    • Raymo3u99's avatar
      Raymo3u99
      Icon for Helper I rankHelper I
      6 years ago

      mwegener  Thanks for the solution. It gives me the right answer. However I am unable to get subtotals on this column if there are more than one paths in a higher group. Any thoughts as to why it won't allow subtotals?