cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KyawMyoTun
Helper II
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.
sale sample.PNG

 
 
9 REPLIES 9
Frank_Fei
Resolver I
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" )
    )

id.PNG

 

@Frank_Fei,
I am getting the below ans.
Working with slicer getting the same result for shop A,B & C.
fill down by sale id-2.PNG

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
Community Support
Community Support

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

 

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.

Hi @v-diye-msft ,

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

Hi @KyawMyoTun 

 

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.

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.
fill down by sale id-1.PNG

amitchandak
Super User
Super User

@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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.