Forum Discussion
cumulative amount
Hello,
I would like to calculate a measure that allows calculating cumulative purchases per suppliers based on a date range filtered by a segment. The table contains fields for suppliers, invoice dates, and invoice amounts. I have created a measure that calculates the purchases. My visual is a matrix with 3 columns: suppliers, the total purchases (for the period filtered by the date segment), and a third column that should display the cumulative purchases.
This cumulative column should, for example, show the value 30 if the amount is 10 in row 1 and 20 in row 2. The cumulative purchases should dynamically adjust based on the order of suppliers displayed, such as ascending alphabetical order, descending alphabetical order, or sorting by the amount column.
I am struggling to find a solution for this.
Thx for your help !!!!
2 Replies
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.Hello,
This is my database named 'Base,' containing the following columns:
- Supplier Invoice Date => column 'Date'
- Supplier => column 'Fournisseur'
- Invoice Amount => column 'Montant'
I would like to create the following table in a visual matrix in Power BI (I know how to create the first two columns):
Fournisseurs
Montant cumulé
Total progressif
% progressif
A
598,06
598,06
23%
B
556,14
1154,2
45%
C
532,53
1686,73
65%
D
473,02
2159,75
84%
E
426,69
2586,44
100%
If the user sorts by the second column, the last two columns should recalculate (example sorting 1):
Fournisseurs
Montant cumulé
Total progressif
% progressif
E
426,69
426,69
16%
D
473,02
899,71
35%
C
532,53
1432,24
55%
B
556,14
1988,38
77%
A
598,06
2586,44
100%
Example sorting 2:
Fournisseurs
Montant cumulé
Total progressif
% progressif
A
598,06
598,06
23%
B
556,14
1154,2
45%
C
532,53
1686,73
65%
D
473,02
2159,75
84%
E
426,69
2586,44
100%
The measure for column 1 is simply: sum('Base'[Amount]).
The measure for column 4, which I am currently using, but it only works well when there are few rows:
Cumul Catégorie % =
VAR _CurrVal = [Montant]
VAR _KpiTotal = CALCULATE( [Montant], ALL( Base[Fournisseur]) )
VAR _KPICumul = CALCULATE( [Montant], FILTER( ALL( Base[Fournisseur] ), [Montant]>= _CurrVal ))
RETURN
DIVIDE( _KPICumul, _KpiTotal)
The PBIX => https://www.dropbox.com/scl/fi/bvk8xgkcbyjak0yjxb98n/total-cumul.pbix?rlkey=pemlcmxzyqzffbe5jmhqsbkmv&dl=0
Thank you in advance.
