Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to create a measure that makes a calculation using the SUMX function but also has a filter, for example SUMX (table; max (year column); column1 * column2) and will also need to create a measure that do the same thing however using the previous year, for example SUMX (table, (max (year column) - '); * column1 column2), made several attempts however unsuccessfully.
The general formula for a measure like that is:
SUMX(FILTER(...),...)
Or you can use CALCULATE
CALCULATE(SUM(...),FILTER(...))
without sample data, not sure how much more I can help.
I'm trying it
(SUMX (FILTER (DMEGF_Ano; max (DMEGF_Ano [Year])) facts, facts[Vl_PedidoCompra] * facts [Qtd_PedidoCompra]))
but it is giving error
I did as suggested however without success, maybe I'm trying to make wrong way, what I need is to get the value of a calculation over a year filtered and also the value of it in anetrior period to the filtrate, for example, I want to know the total sales of the year 2016 and in a different measure present value of 2015
Try this...
Measure = CALCULATE(SUM(Sales[Sales])*SUM(Sales[QTY]), FILTER(CalendarTable, MAX(CalendarTable[Year])))
@vitexo87, @Sean's formula assumes a relationship between the Sales table and Calendar table based upon a Date field.
To get the 2015, just add a "-1", like "MAX(CalendarTable[Year])-1"
Sticklers would tell you to do it in three measures:
PedidoCompra = facts[Vl_PedidoCompra] * facts [Qtd_PedidoCompra]
ThisYear = CALCULATE(PedidoCompra,FILTER (DMEGF_Ano; max (DMEGF_Ano [Year])))
LastYear = CALCULATE(PedidoCompra,FILTER (DMEGF_Ano; max (DMEGF_Ano [Year])-1))
I have two tables that use for pedro filter that are the tables of the year and month
this way the measure tells me the total amount changing it when it when the filter is changed but adding -1 he did not show the
value of the previous year,
Measure = CALCULATE(SUMX(Fatos; Fatos[Vl_PedidoCompra]*fatos[Qtd_PedidoCompra]); FILTER(DMEGF_Ano; MAX(DMEGF_Ano[Ano])-1))
My bad, try:
Measure = CALCULATE(SUMX(Fatos; Fatos[Vl_PedidoCompra]*fatos[Qtd_PedidoCompra]); FILTER(DMEGF_Ano; DMEGF_Ano[Ano] = MAX(DMEGF_Ano[Ano])-1))
Now work out, is there any function I can subistituir the MAX function, because with the Max function I'll just have the value 2015 if I apply the 2015 filter the returned value will be blank
@vitexo87 - I don't understand what you are asking, you can substitute just about anything in there, even = 2015 if you want to hard code it.
This is why it's good to have a calendar table? I thought you have one?
you can then do Year to Date and Previous Year - which will adjust with the slicers
The dates of tables I have are two, one that tells the year and one that informs the month both are relacionan through the fact table, just need to now see a way to use this as VOES passed me without the MAX function in time determine the year to calculate
@vitexo87 These Measure give Year and Previous Year and Year on Year Change and Year On Year %
Add the Year Slicer and then select at least 2 consective years!
Max Year = CALCULATE([Total Net], FILTER(Payments, Payments[PMT Year]=MAX(Payments[PMT Year])))
Max Year -1 = CALCULATE([Total Net], FILTER(Payments, Payments[PMT Year]=MAX(Payments[PMT Year])-1))
YoY Change = [Max Year] - [Max Year -1]
YoY % Change = DiVIDE ( [YoY Change], [Max Year -1], 0)
Let me know if you do decide to add a Caledar Table - there's a better way to do this.
I tried the following expression and the value is blank:
Consumption Warehouse 2 = calculate (sum (Facts [Vl_ConsumoAlmox]) - sum (Facts [Vl_EstornoAlmox]), FILTER (DMEGF_Ano; DMEGF_Ano [Year] = MAX (DMEGF_Ano [Year]) - MAX (DMEGF_Ano [Year]) - 1))
Consumption Warehouse 2 = calculate (sum (Facts [Vl_ConsumoAlmox]) - sum (Facts [Vl_EstornoAlmox]), FILTER (DMEGF_Ano; DMEGF_Ano [Year] = (MAX (DMEGF_Ano [Year]) - 1)))
2016 - ( 2016 - 1) => 1
FILTER (DMEGF_Ano; DMEGF_Ano [Year] = MAX (DMEGF_Ano [Year]) - (MAX (DMEGF_Ano [Year]) - 1)))
delete the underlined part
correct the error and the information is being displayed however when I do a filter annually whether in 2016, 2015 or 2014 values are blank
are you using DMEFG_Ano[Year] for the Slicer?
Do you have any other filters/slicers ON at the same time?
EDIT:
If you want to create a Date Table look here (Link by Matt)
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |