cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Agregate and filter

Hi Power BI Community

 

I have a transaction table below.

I need to create a report filtered by SALE DATE, and PRODUCT ID.

In the report i want to show:

1- The number of transactions including these products

2- The total value of those transactions (including products not filtered)

 

1 is easy. I just need to use a distinctcount of the ticket ids. Then i apply any filter and it works fine

But 2 is complicated because i need to calculate The total value of each transaction (i.e. the SUM of SALE AMOUNT per transaction) then apply the filters (SALE DATE and PRODUCT ID) and SUM the Total value of alll filtered transactions. 

 

TICKET IDSALE_DATELINE NBSHOP IDPRODUCT IDSALE QUANTITYSALE AMOUNT
3260523622/04/2018 00:002248191152
3260523622/04/2018 00:004247147125,2
3260523622/04/2018 00:00525349510
3260523722/04/2018 00:00225023410
3260523722/04/2018 00:00424900415,95
3260523722/04/2018 00:00524731818
3260523822/04/2018 00:00224736013,33
3260523822/04/2018 00:00324931113,33
3260523822/04/2018 00:00425283613,34
3260523822/04/2018 00:00625349510
3260523822/04/2018 00:00725381110
3260523822/04/2018 00:008247634110
3260523922/04/2018 00:00224918919
3260523922/04/2018 00:00325213919
3260523922/04/2018 00:00428998112
3260523922/04/2018 00:00529014110
3260523922/04/2018 00:00629019110
3260524022/04/2018 00:00225372810
3260524022/04/2018 00:00325439810
3260524022/04/2018 00:00425236628
3260524022/04/2018 00:005253812314,85
3260524122/04/2018 00:00124099112
3260524122/04/2018 00:002246081145
3260524222/04/2018 00:001219018226
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Agregate and filter

I actually found another solution.

 

I have 2 tables : 1 sale detail and 1 sale header. 

I found a way to create a measure in sale detail that points to the sale header using USERRELATIONSHIP.

 

It works.

Thanks

F

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Agregate and filter

Hi @fabienrolland,

 

Believe that the you want the calculation made by Quantiy * Sales amount

 

Ue something like this measure:

Measure = SUMX(Sales;Sales[SALE AMOUNT]*Sales[SALE QUANTITY])

Regards

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: Agregate and filter

Hi MFelix.

Thanks but that is not what i'm looking for.
What you propose is a measure to calculate the total sales amount which is great but the problem is i need to filter product ids and calculate the total value of the transactions that include these product ids.

 

Example:

Let's take 2 tickets 32605236 and 32605237 (the first 2 in my list)

If i use your formula, which allows me to calculate the Sale Amount for each line. I get this.

Now, what i need is to calculate the total for each ticket, so:

32605236 = 77.2

32605237 = 13.95

 

Now to complicate things even more, in my report, i need a measure that shows those totals even when i filter on the product for ex: In this case let's say i filter on product 48191, if i sum your formula, i will get 52, when what i want to show is 77.2.

In other words, i want to filter on transactions containing 1 product and show the total value of the transactions containing this product. 

 

 

TICKET IDSALE_DATELINE NBSHOP IDPRODUCT IDSALE QUANTITYSALE AMOUNTMeasure
3260523622/04/2018 00:00224819115252
3260523622/04/2018 00:004247147125.225.2
3260523622/04/2018 00:005253495100
3260523722/04/2018 00:002250234100
3260523722/04/2018 00:00424900415.955.95
3260523722/04/2018 00:005247318188
Highlighted
Super User III
Super User III

Re: Agregate and filter

Hi @fabienrolland,

Measures are calcilated based on context so depending on the columns you put on your visual the result will be cslculated if you add all the.coçumns to.your table you will only see the values per row if you renove columns the totals will be calculated based on that new context.

In your case if you have only the tickets and the values you will get 77.2 if you want to have the full detail ob your table the.measure can.be change however you will get the 77.2 in all rows.


So.my question is what is the.context you want on your.visual (what.columns) and if you have lots of details do.you want to get repeted values or not?

Regards.
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: Agregate and filter

I would like to create a table filtered by date (SALE_DATE) and product (PRODUCT ID)

 

The table would include Store ID in lines and in columns :

- Nb of transactions (that's DISTINCTCOUNT(TICKET ID)

- Total amount of products filtered (SUMX(SALE AMOUNT * SALE QUANTITY)

- Total value of transactions that include the filtered product (the one measure i need help on)

 

Thanks a million for your help

Highlighted
Solution Specialist
Solution Specialist

Re: Agregate and filter

@fabienrolland Can you share a snapshot of the output ? Especially for the last measure

Highlighted
Frequent Visitor

Re: Agregate and filter

I actually found another solution.

 

I have 2 tables : 1 sale detail and 1 sale header. 

I found a way to create a measure in sale detail that points to the sale header using USERRELATIONSHIP.

 

It works.

Thanks

F

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors