cancel
Showing results for
Did you mean:
Helper II

## 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.

9 REPLIES 9
Resolver I

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

Helper II

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

Resolver I

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

Community Support

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Helper II

Hi @v-diye-msft ,

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

Community Support

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

Please try again and let me know if it works.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Helper II

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.

Super User

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

Then you can use a measure

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

Or use slicer or visual level filter

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper II

@amitchandak,

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!