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.
Hello,
I'm designing a report for a sales analysis. However, I have the next problem. I want to present a cumulative sales per day and per service and region of my company but when I show this, the cumulative does not show the correct amount but repeats the total sum.
But when I don't use the field region or service the cumulative per day is correct
Actually I am using the next formula:
cumulative = SUMX(FILTER(ALLSELECTED('table_sales'),'table_sales'[date]<=MAX('table_sales'[date])),'table_sales'[quantity])
When I use the tipical formula I don´t have any difference:
cumulative = CALCULATE (
SUM ( 'table_sales'[quantity] ),
FILTER (
ALLSELECTED ( 'table_sales'),
'table_sales'[date] <= MAX ( 'table_sales'[date] )
)
)
I expect have the next result:
Is it necessary add any other function to my formula?
Solved! Go to Solution.
Hi, one way to solve this:
Create a calculated Column
Index = VAR RegionRank = RANKX ( Table1, Table1[Region],, DESC, DENSE ) VAR ServiceRank = RANKX ( Table1, Table1[Service],, DESC, DENSE ) RETURN ( [Day] * 100 ) + ( RegionRank * 10 ) + ServiceRank
After that Create a New Measure
RunningTotal = CALCULATE ( SUM ( 'Table1'[Sales] ), FILTER ( ALLSELECTED ( Table1[Index] ), Table1[Index] <= MAX ( Table1[Index] ) ), ALLEXCEPT ( Table1, Table1[Index] ) )
Maybe You need some adjust to sort the table visual.
Regards
Victor
Lima - Peru
Hi, one way to solve this:
Create a calculated Column
Index = VAR RegionRank = RANKX ( Table1, Table1[Region],, DESC, DENSE ) VAR ServiceRank = RANKX ( Table1, Table1[Service],, DESC, DENSE ) RETURN ( [Day] * 100 ) + ( RegionRank * 10 ) + ServiceRank
After that Create a New Measure
RunningTotal = CALCULATE ( SUM ( 'Table1'[Sales] ), FILTER ( ALLSELECTED ( Table1[Index] ), Table1[Index] <= MAX ( Table1[Index] ) ), ALLEXCEPT ( Table1, Table1[Index] ) )
Maybe You need some adjust to sort the table visual.
Regards
Victor
Lima - Peru
Thanks @Vvelarde! The help you gave me by private messagges was very good. I have some fields which help me to classify my data and them have to add in an exception.
The final formula was:
RunningTotal =
CALCULATE(
SUM('Table1'[Sales]);FILTER(ALLSELECTED(Table1[Index]);Table1[Index]<=MAX(Table1[Index]));ALLEXCEPT(Table1;Table1[Index];Table1[Region];Table1[Service]))
Hi, I still have the same problem. The cumulative doesn't show like I expect. We can continue the conversation in spanish by private messages?
Hi @arturocastillo,
You can firstlt add an index column in Query Editor mode.
Then, create a measure like below:
Running value = CALCULATE ( SUM ( table_sales[sales] ), FILTER ( ALL ( table_sales ), table_sales[Index] <= MAX ( table_sales[Index] ) ) )
Best regards,
Yuliana Gu
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |