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.
Dears,
I am currently training getting more familiar with Dax in Power BI, and I try to do a running total by currency.
To do so, I am using the AdventureWorksDW2017 database and then FactInternetSales, with a relationship to DimCurrencyTable.
I created a matrix in which I group rows by CurrencyName and SalesOrderNumber and I display a measure called Sales Amount which derived from the column SaleAmount (Sales Amount = SUMX(FactInternetSales,FactInternetSales[SalesAmount]))
To calculate the running amount, I understood that I need a ranking therefore I created a SalesOrderIndex in FactInternetSales table and then created this measure:
Solved! Go to Solution.
The reason that you are seeing repeating Cumulative sales amount is because the measure relies on the rank of the sales amount. If the sales amount per order is the same, then you get the same rank,
There is a hack to solve this (in a random sort of way, since you need to break the rank for equal sales values. The way to do this is:
Create a new column in the table which adds a minute amount to each sales value per order, such as :
You can now use this column to establish the rank by order number, as in:
Rank (Random) Sales =
IF (
NOT ( ISBLANK ( [Sum Random sales] ) ),
RANKX (
ALL ( 'Summary Table'[SalesOrderNumber] ),
[Sum Random sales],
,
DESC,
DENSE
)
)
Now that you have a new rank by order number, you can calculate the cumulative for the original sales amount based on this rank using:
Running total Sales Amount (random) =
VAR RNK = [Rank (Random) Sales]
RETURN
IF (
ISINSCOPE ( 'Summary Table'[SalesOrderNumber] ),
CALCULATE (
[Summary Sales],
FILTER (
ALLSELECTED ( 'Summary Table'[SalesOrderNumber] ),
[Rank (Random) Sales] <= RNK
)
)
)
And you will get
Attached is the new sample file
Proud to be a Super User!
Paul on Linkedin.
Dears,
I am still stuck with this Running total... 😞
How can I join my pbix file, so that it will more convenient to help me?
Thanks.
You can share a PBIX file using cloud services (OneDrive, Google Drive...)
Proud to be a Super User!
Paul on Linkedin.
Thanks Paul!
Here after is the link to access the pbix file: https://1drv.ms/u/s!AtrphPjJln_zisEB-dDvCb825JBjPg?e=9LtB3Y
I created a measure "Sale Amount RT by Currency" in FactInternetSales which should to a running total by currency and Sales Order Nb.
I don't know what I miss but it does not work.
Thanks for the file.
First of all, a heads up on the onus that iteration measures have on processing times for measures. I created the RANK measure and running total for your model, but the processing time for the model makes it unworkable. So I created a summary table within the model (Summary Table) to illustrate the measures.
This is the structure you need:
Summary Sales = SUM('Summary Table'[Sales])
Rank Sales = IF(NOT(ISBLANK([Summary Sales])), RANKX(ALL('Summary Table'[SalesOrderNumber]), [Summary Sales], , DESC,Dense))
Running total Sales Amount =
VAR RNK = [Rank Sales]
RETURN
IF (
ISINSCOPE ( 'Summary Table'[SalesOrderNumber] ),
CALCULATE (
[Summary Sales],
FILTER (
ALLSELECTED ( 'Summary Table'[SalesOrderNumber] ),
[Rank Sales] <= RNK
)
)
)
and you get
I've attached the sampe PBIX
Proud to be a Super User!
Paul on Linkedin.
Thank you very much for your help Paul. I need time to understand the differents steps you went throw..However when I look at the results in the print-screen, it seems that the sales amount are not cumulating but repeating...I was expecting to see that for each currency change, the Sales AMount starts from 0 then summing up when running throw all the sales orders contained in the currency.
Thanks again.
The reason that you are seeing repeating Cumulative sales amount is because the measure relies on the rank of the sales amount. If the sales amount per order is the same, then you get the same rank,
There is a hack to solve this (in a random sort of way, since you need to break the rank for equal sales values. The way to do this is:
Create a new column in the table which adds a minute amount to each sales value per order, such as :
You can now use this column to establish the rank by order number, as in:
Rank (Random) Sales =
IF (
NOT ( ISBLANK ( [Sum Random sales] ) ),
RANKX (
ALL ( 'Summary Table'[SalesOrderNumber] ),
[Sum Random sales],
,
DESC,
DENSE
)
)
Now that you have a new rank by order number, you can calculate the cumulative for the original sales amount based on this rank using:
Running total Sales Amount (random) =
VAR RNK = [Rank (Random) Sales]
RETURN
IF (
ISINSCOPE ( 'Summary Table'[SalesOrderNumber] ),
CALCULATE (
[Summary Sales],
FILTER (
ALLSELECTED ( 'Summary Table'[SalesOrderNumber] ),
[Rank (Random) Sales] <= RNK
)
)
)
And you will get
Attached is the new sample file
Proud to be a Super User!
Paul on Linkedin.
Hello Paul,
First of all, sorry for my late answer, I had quite a heavy workload end of last week and did not have time so far to revert.
Your explanation is quite clear! I will study your 2nd pbix file to understand it in details.
Thanks again.
Hello,
Any other suggestions or ideas to help me?
@Sylvain74 , which table you have CurrencyName
You can try like
Sales Amount RT by Currency =
VAR MaxSalesOrderIndex = MAX(FactInternetSales[SalesOrderIndex])
RETURN
CALCULATE([Count Sales Order Nb], filter(FactInternetSales, FactInternetSales[SalesOrderIndex]<= MaxSalesOrderIndex && FactInternetSales[CurrencyName] = max(FactInternetSales[CurrencyName])))
Hi @amitchandak ,
I tried below dax statement where I replace CurrencyName by CurrencyKey since it does not exists in FactInternetSales. However it does not work...For each line, it gives the same amount thant the Sales Amount
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |