Forum Discussion
cumulative amount
Community ChampionPlease 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.