Forum Discussion

samc_26's avatar
samc_26
Helper IV
2 years ago

Row level filter with calculation

Hi all, I have a table with a list of costs against different people but for one person I want to give them a 50% discount, in this case I'll use Charlie as the chosen one! So I want the £200 to stay in the raw data file but the total should include giving him a discount, making the total £425 instead of £525.

 

I'm guessing this requires a row level filter and maybe more than one calculation but everything I've tried so far hasn't worked, can anyone suggest a solution please? Hoping it can be done somehow!

 

Any help greatly appreciated! 

 

Need Help

11 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, samc_26 

    try below measure

    just adjust your table name and column name

     

    Measure = 
var a = SELECTEDVALUE('Table (3)'[name])
var b = SUMX(  FILTER( 'Table (3)' ,'Table (3)'[name]=a),'Table (3)'[charge])
var c = b* (50/100)
var d = CALCULATE(SUM('Table (3)'[charge]),ALL())
return
IF(ISINSCOPE('Table (3)'[name]),MIN('Table (3)'[charge]),d-c)

     

     

    check as you need 

     

    • samc_26's avatar
      samc_26
      Helper IV
      2 years ago

      Hi, thank you for taking the time to post this, I have tried to use it but I can't work out how the code filters to just the name 'Charlie'? When I put the measure in a card it shows a figure of 2.89M when I'm looking for about 11k so not sure what's happened! 

      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        hi, samc_26 

         

        i made code for dynamic user selection 

        for only charlie

        try below code 

         

        Measure = 
var b = SUMX(  FILTER( 'Table (3)' ,'Table (3)'[name]="charlie"),'Table (3)'[charge])
var c = b* (50/100)
var d = CALCULATE(SUM('Table (3)'[charge]),ALL())
return
IF(ISINSCOPE('Table (3)'[name]),MIN('Table (3)'[charge]),d-c)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago
    Create e new Measure and assign factor for selected value
     
    Net Sales Factored =
    var _Category = CALCULATE( [Net Sales] * 1.5, Category[Product Category] = "Audio")
    var _SelectedCategory = SELECTEDVALUE(Category[Product Category])
    return switch(true, _SelectedCategory = "Audio", _Category, [Net Sales])
    • samc_26's avatar
      samc_26
      Helper IV
      2 years ago

      Hi, thank you for your post, I've tried to use it but I'm not sure what rhe measure 'Net Sales' is in this example, is it just a sum of all sales from the charges column?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Yes, [Net Sales] is another measure that is just the sum of sales. It is to show, you create the original measure first, then you can use it another measure

        Net Sales = SUM( Sales[SalesAmount])
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi samc_26 ,

    Here are some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    1.Create two measures

     

    Total with Discount = 
SUMX(
    'Table',
    IF(
        'Table'[Name] = "Charlie",
        'Table'[Charge] * 0.5,
        'Table'[Charge]
    )
)
    Measure = 
IF(
    ISINSCOPE('Table'[Name]),
    sum('Table'[Charge]),
    [Total with Discount])

     

    2.Final Output

    Best Regards,

    Albert He

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

    • samc_26's avatar
      samc_26
      Helper IV
      2 years ago

      Hi Albert, thank you for the ideas but it's not really had any effect on the data, i'm not sure if it's because I've got multiple weeks on my data or if it's something else? I can't really show you anything as it's business confidential 😞 

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, samc_26 

     

     

    Measure = 
var b = SUMX(  FILTER( 'Table' ,'Table'[name]="charlie"),'Table'[charge])
var c = b* (50/100)
var d = CALCULATE(SUM('Table'[charge]),ALL())
return
IF(ISINSCOPE('Table'[name]),MIN('Table'[charge]),d-c)