Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi
I have created a cumulative sum meassure that calculates order volume cumulative sum.
my data contains, <OrderCreationDate>, <order_type>, <licences>
and my meassure works on the <licences> column. I plot the measure over time with the line chart and I have certain report-level and page level filters on the <order_type> which excludes certain order types.
however, the measure ignores these filters and calculates and plots the sum of all orders regardless of filters.
Cumulative sales = CALCULATE(sum(O365LicenseReport[Licenses]),FILTER(all(O365LicenseReport), O365LicenseReport[OrderCreationDate]<=max(O365LicenseReport[OrderCreationDate])))
I even tried to impose visual revel filters and the funny thing is that the order types I want to exclude do not appear in the options because they have been filtered out by the report and page-level filters ????
what am I missing here?
Solved! Go to Solution.
Hi @Anonymous,
If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?
You could try to modify your formula like below, that should be your expected.
Cumulative sales = CALCULATE ( SUM ( O365LicenseReport[licences] ), FILTER ( ALLSELECTED ( 'O365LicenseReport' ), O365LicenseReport[OrderCreationDate] <= MAX ( O365LicenseReport[OrderCreationDate] ) ) )
Here is my test output.
Hope this can help you!
Best Regards,
Cherry
@v-piga-msft wrote:Hi @Anonymous,
If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?
You could try to modify your formula like below, that should be your expected.
Cumulative sales = CALCULATE ( SUM ( O365LicenseReport[licences] ), FILTER ( ALLSELECTED ( 'O365LicenseReport' ), O365LicenseReport[OrderCreationDate] <= MAX ( O365LicenseReport[OrderCreationDate] ) ) )Here is my test output.
Hope this can help you!
Best Regards,
Cherry
YES!!!!!
THANK YOU !!!!!!
yes !!! now it works... I understand that I should have used the "ALLSELECTED" rather the "ALL" statement right?
if I understand it correctly, ALLSELECTED (as implied) respects filters where as ALL ignores them, correct?
Hi @Anonymous,
If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?
You could try to modify your formula like below, that should be your expected.
Cumulative sales = CALCULATE ( SUM ( O365LicenseReport[licences] ), FILTER ( ALLSELECTED ( 'O365LicenseReport' ), O365LicenseReport[OrderCreationDate] <= MAX ( O365LicenseReport[OrderCreationDate] ) ) )
Here is my test output.
Hope this can help you!
Best Regards,
Cherry
@v-piga-msft wrote:Hi @Anonymous,
If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?
You could try to modify your formula like below, that should be your expected.
Cumulative sales = CALCULATE ( SUM ( O365LicenseReport[licences] ), FILTER ( ALLSELECTED ( 'O365LicenseReport' ), O365LicenseReport[OrderCreationDate] <= MAX ( O365LicenseReport[OrderCreationDate] ) ) )Here is my test output.
Hope this can help you!
Best Regards,
Cherry
YES!!!!!
THANK YOU !!!!!!
yes !!! now it works... I understand that I should have used the "ALLSELECTED" rather the "ALL" statement right?
if I understand it correctly, ALLSELECTED (as implied) respects filters where as ALL ignores them, correct?
You have Filter(all.... in your measure. I believe that is going to return all rows irrespective of visual level filters.
Can you post a pbix file with expected output?
unfortunatelly I have actual sales data, can't post the pbix. I will try to recreate with dummy data
I see
so how do it tell it "respect filters or slicers in the page" ?
i don't want to hardwire the filters in the measure code cause that is not make the measure interactive ( I guess I'm stating the obvious)..
I dont think you need to use the filter command at all. The syntax for calculate is calculate(aggregate, filter1, filter 2...). That may work
I just tried that but unfortunatelly DAX complains about the MAX function. I tried
Cumulative sales = CALCULATE(
sum(O365LicenseReport[Licenses]),
O365LicenseReport[OrderCreationDate]<=max(O365LicenseReport[OrderCreationDate])
)
and got back
A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Chronis69,
You Max function and the less than equal to column are referring to the same column. I would create a new column with the max value of order creation date like
max val = max(O365LicenseReport[OrderCreationDate]).[Date])
Then
Cumulative sales = CALCULATE(
sum(O365LicenseReport[Licenses]),
O365LicenseReport[OrderCreationDate]<=YOURTABLENAME[maxval])
@prakritnepal wrote:Chronis69,
You Max function and the less than equal to column are referring to the same column. I would create a new column with the max value of order creation date like
max val = max(O365LicenseReport[OrderCreationDate]).[Date])
Then
Cumulative sales = CALCULATE(
sum(O365LicenseReport[Licenses]),
O365LicenseReport[OrderCreationDate]<=YOURTABLENAME[maxval])
@prakritnepal as per your suggestion I created two columns
1.
__test max val = max(O365LicenseReport[OrderCreationDate].[Date])
2.
__test run rate =
CALCULATE(SUM(O365LicenseReport[Licenses]),O365LicenseReport[OrderCreationDate]<=O365LicenseReport[__test max val])
but then DAX complains that
The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.
Why does it have to be so freaging hard to create even the simplest things with DAX??? (btw, I don't understand why it complains, it looks correct to me)
Hi @Anonymous,
Have you tried using the quick measure feature to create a cumulative sum?
Proud to be a Super User!
@danextian wrote:Hi @Anonymous,
Have you tried using the quick measure feature to create a cumulative sum?
I just did. this creates and even more unwated effect.
the code that the auto measure generates is the following
Licenses running total in OrderCreationDate =
CALCULATE(
SUM('O365LicenseReport'[Licenses]),
FILTER(
ALLSELECTED('O365LicenseReport'[OrderCreationDate]),
ISONORAFTER('O365LicenseReport'[OrderCreationDate], MAX('O365LicenseReport'[OrderCreationDate]), DESC)
)
)
what it does is that it resets the running total accoring to the resolution of the visual. for example if the line charts has years as the X axis it presents the running total each year indepentently. i.e. if the sales are 1000@ y1, 2000@y2 and 3000@y3 instead of a line with points at
1000, 3000 (=1000y1+2000y2), 6000 (=3000y2+3000y3) it gives a line of
1000, 2000, 3000... disaster 😉 ... useful but I don't need a complicated DAX measure for this I simply do a plot and ask to do the sum... see the black line in the pic below
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |