summarize based on sale Id

[KyawMyoTun]

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.

[Anonymous]

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]

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

[Anonymous]

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

[v-diye-msft]

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

[KyawMyoTun]

Hi @v-diye-msft ,

  Thanks a lot for your info.
But I am getting the below error while trying accrodingly.

[v-diye-msft]

Hi @KyawMyoTun 

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

Please try again and let me know if it works.

[KyawMyoTun]

Hi @v-diye-msft ,

  Thanks a lot for your help.
The filling down the shop name columns are working.
But I found that if there has no shop name in soure, the above shop name was adopted.
I'd like to put that as blank.
The sale id - 4444's shop name should be blank.

I am sorry that I didn't mention about the blank shop name.

[amitchandak]

@KyawMyoTun , If the data is the format you have shown. Then first use fill GAP

https://www.youtube.com/watch?v=-fwKhMot9hw

Then you can use a measure

calculate(sum(Table[Amount]), Table[Place]<>"C")

Or use slicer or visual level filter

[KyawMyoTun]

@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?