Forum Discussion

KyawMyoTun's avatar
KyawMyoTun
Icon for Helper IV rankHelper IV
6 years ago

summarize based on sale Id

Dear Experts,

   I am trying to get the summarized sales amount by shop name based on sale id.
I want to get the result as below.
1) the sale amount based on the shop name (e.g Shop A sale = 32, Shop B sale = 28)
2) I want to exclude the Place CC. so, the total sale should be 60.
Plz help me to write a measure to get above.

 
 
Need Help

9 Replies

    • KyawMyoTun's avatar
      KyawMyoTun
      Icon for Helper IV rankHelper IV
      6 years ago

      amitchandak,

        Thanks for your help.
      My dataset is too large and that is only the sample format.
      Is there any other way without filling in power query.
      Currently the user behaviour is still unstable and they sometime input shop name but sometime not.
      Filling down shop name might also have some issue because of blank shop name at the moment.
      Can you please help?

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi KyawMyoTun 

     

    1) the sale amount based on the shop name (e.g Shop A sale = 32, Shop B sale = 28)

    You can do this by adding the calculated column if you don't like the power query:

    Column = var a = CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[shop name]<>BLANK()&&[Index]<EARLIER('Table'[Index])))
var b = CALCULATE(MAX('Table'[shop name]),FILTER(ALL('Table'),[Index]=a))
Return
IF('Table'[shop name]=BLANK(),b,'Table'[shop name])
    Column 2 = var a = CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[item]<>BLANK()&&[Index]<EARLIER('Table'[Index])))
var b = CALCULATE(MAX('Table'[item]),FILTER(ALL('Table'),[Index]=a))
Return
IF('Table'[item]=BLANK(),b,'Table'[item])


    2) I want to exclude the Place CC. so, the total sale should be 60.

    Measure = CALCULATE(SUM('Table'[Amount]),FILTER('Table',[Place]<>"CC"))

     

      • v-diye-msft's avatar
        v-diye-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi KyawMyoTun 

         

        Sorry, I forgot to say, you need to add an index column.

        Please try again and let me know if it works.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi KyawMyoTun ,

     

    AMBS = 
VAR IDS =
    VALUES ( 'Table'[Sale id] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale id] IN IDS )
    )
    AMBSwithoutc = 
VAR IDS =
    VALUES ( 'Table'[Sale id] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale id] IN IDS && 'Table'[Place] <> "CC" )
    )

     

    • KyawMyoTun's avatar
      KyawMyoTun
      Icon for Helper IV rankHelper IV
      6 years ago

      Anonymous,
      I am getting the below ans.
      Working with slicer getting the same result for shop A,B & C.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi KyawMyoTun ,

         

        1. Create a calculated table as below.

        Shope name = DISTINCT('Table'[shop name])

        2. Update the measures

        AMBS = 
VAR IDS =
    CALCULATETABLE(VALUES ( 'Table'[Sale id] ),FILTER('Table','Table'[shop name] in VALUES('Shope name'[shop name])))
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (  'Table' , 'Table'[Sale id] IN IDS )
    )
        AMBSwithoutc = 
VAR IDS =
    CALCULATETABLE(VALUES ( 'Table'[Sale id] ),FILTER('Table','Table'[shop name] in VALUES('Shope name'[shop name])))
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (  'Table' , 'Table'[Sale id] IN IDS && 'Table'[Place] <> "CC" )
    )