Giving values to filtered column

[ALRUYOYO]

Hi,

I took a screenshot of table view. And then of a sample of what I am trying to accomplish.

So I would like to add a column to table view which assigns levels if the total amount per month is < 7500, <20.000 etc.

The problem is that I can only use visual level filters on total amount. And could not find a way how to add new column to report data and not the dataset tables.

I want to make a dashboard which shows the proportion of restaurant levels. So at the beginning of the month all fall into level one, but as the month progresses you can see the shift in levels on the bar chart.

I hope someone can help me. And I am looking forward to participate more in this comunnity.

Regards,

Algirdas

[Anonymous]

@ALRUYOYO

1.You need to create a static table for the band. Name the table as Band.

2. The columns should be Position, BandDescription, MinValue, MaxValue

3. The rows could be something thing like

     1, < 7500, 0, 7500

     2, 7501 - 20000, 7501,20000

     3, 20001 - 30000, 20001, 30000

and so on depending on the band that you want to go upto

4. Create a column in the fact table defined as

    GroupPosition = CALCULATE (
                                 VALUES ( Band[Position] ),
                                     FILTER (
                                               Band,
                                            FactTable[Value] >= Band[MinValue]
                                                && FactTable[Value] <= Band[MaxValue]
                                                    )
                                             )

 5. Add this to the table that you are displaying.

Try this out.

If you find this working please acccept it as a solution and also give Kudos.

Cheers

CheenuSing

[Anonymous]

@ALRUYOYO

1.You need to create a static table for the band. Name the table as Band.

2. The columns should be Position, BandDescription, MinValue, MaxValue

3. The rows could be something thing like

     1, < 7500, 0, 7500

     2, 7501 - 20000, 7501,20000

     3, 20001 - 30000, 20001, 30000

and so on depending on the band that you want to go upto

4. Create a column in the fact table defined as

    GroupPosition = CALCULATE (
                                 VALUES ( Band[Position] ),
                                     FILTER (
                                               Band,
                                            FactTable[Value] >= Band[MinValue]
                                                && FactTable[Value] <= Band[MaxValue]
)
)

[chrisu]

I would do this by adding a calculated column that categorizes TotalAmount into levels using the SWITCH function.  See the example at http://www.powerpivotpro.com/2012/06/dax-making-the-case-for-switch/ for info on SWITCH.  Your formula would be something like:

Level=SWITCH(TRUE(),
             [TotalAmount]<7500, “Lvl1”,

             AND([TotalAmount]>=7500, [TotalAmount]<20000), “Lvl2”,

             AND([TotalAmount]>=20000, [TotalAmount]<=50000), “Lvl3”,
             “Lvl4”
           )

Then you would add a measure that counts that levels and that is what you would use on your chart.  It would look something like:

LevelCount = CALCULATE(COUNTA([Level]))

[ALRUYOYO]

Thank you for your reply.

Unfortunatelly it is not possible to add calculated columns in direct query. At least I get this error message.