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.
I have this matrix below and from those data I would like to build a line chart with the cumulative %. I saw some answer about that but cannot figure it out how to make it work.
Hi @Anonymous ,
You can create measure like DAX below.
Measure1 = CALCULATE(SUM(Table1[M+XX column]),FILTER(ALLSELECTED(Table1), Table1[Year] =MAX(Table1[Year])&&Table1[Quarter] <=MAX(Table1[Quarter])))
Or could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @v-xicai
Here an simplified extract of my table "Associations"
id | date_inscription | first_payment_date | Temps d'activation |
1139929 | 2018-12-01 00:00:00 | 2018-12-10 00:00:00 | M+00 |
1139933 | 2018-12-01 00:00:00 | 2018-12-10 00:00:00 | M+00 |
1139936 | 2018-12-01 00:00:00 | 2019-01-09 00:00:00 | M+01 |
1139937 | 2018-12-01 00:00:00 | 2019-01-07 00:00:00 | M+01 |
1139938 | 2018-12-01 00:00:00 | 2019-06-07 00:00:00 | M+06 |
1139940 | 2018-12-01 00:00:00 | 2019-01-06 00:00:00 | M+01 |
1139943 | 2018-12-01 00:00:00 | 2018-12-04 00:00:00 | M+00 |
1139945 | 2018-12-01 00:00:00 | 2018-12-03 00:00:00 | M+00 |
1139946 | 2018-12-01 00:00:00 | 2019-02-25 00:00:00 | M+02 |
1139951 | 2018-12-01 00:00:00 | 2018-12-01 00:00:00 | M+00 |
1139952 | 2018-12-01 00:00:00 | 2018-12-25 00:00:00 | M+00 |
1139956 | 2018-12-01 00:00:00 | 2019-01-15 00:00:00 | M+01 |
1139957 | 2018-12-01 00:00:00 | 2018-12-06 00:00:00 | M+00 |
1139960 | 2018-12-01 00:00:00 | 2018-12-02 00:00:00 | M+00 |
1139961 | 2018-12-01 00:00:00 | 2018-12-02 00:00:00 | M+00 |
1139963 | 2018-12-01 00:00:00 | 2018-12-16 00:00:00 | M+00 |
1139964 | 2018-12-01 00:00:00 | 2019-01-28 00:00:00 | M+01 |
1139965 | 2018-12-01 00:00:00 | 2018-12-04 00:00:00 | M+00 |
1139966 | 2018-12-01 00:00:00 | 2018-12-02 00:00:00 | M+00 |
1139969 | 2018-12-01 00:00:00 | 2019-03-27 00:00:00 | M+03 |
1139970 | 2018-12-01 00:00:00 | 2019-01-05 00:00:00 | M+01 |
1139971 | 2018-12-01 00:00:00 | 2018-12-26 00:00:00 | M+00 |
1139972 | 2018-12-01 00:00:00 | 2019-03-11 00:00:00 | M+03 |
1139973 | 2018-12-01 00:00:00 | 2018-12-09 00:00:00 | M+00 |
1139974 | 2018-12-01 00:00:00 | 2019-01-29 00:00:00 | M+01 |
1139975 | 2018-12-01 00:00:00 | 2019-02-09 00:00:00 | M+02 |
1139976 | 2018-12-01 00:00:00 | 2018-12-12 00:00:00 | M+00 |
1139979 | 2018-12-01 00:00:00 | 2018-12-03 00:00:00 | M+00 |
1139981 | 2018-12-01 00:00:00 | 2018-12-01 00:00:00 | M+00 |
1139982 | 2018-12-01 00:00:00 | 2019-02-05 00:00:00 | M+02 |
1139987 | 2018-12-01 00:00:00 | 2018-12-24 00:00:00 | M+00 |
1139988 | 2018-12-02 00:00:00 | 2019-05-14 00:00:00 | M+05 |
1139990 | 2018-12-02 00:00:00 | 2019-01-19 00:00:00 | M+01 |
1139996 | 2018-12-02 00:00:00 | 2018-12-06 00:00:00 | M+00 |
1139998 | 2018-12-02 00:00:00 | 2018-12-14 00:00:00 | M+00 |
1139999 | 2018-12-02 00:00:00 | 2018-12-05 00:00:00 | M+00 |
1140001 | 2018-12-02 00:00:00 | 2019-02-02 00:00:00 | M+02 |
1140003 | 2018-12-02 00:00:00 | 2018-12-03 00:00:00 | M+00 |
1140005 | 2018-12-02 00:00:00 | 2018-12-07 00:00:00 | M+00 |
The columns 2 and 3 are from my DB, the 1 last one is a calculated column to find the numbers of months between those 2 dates.
Now my goal is, on a dashboard with a time slicer, to build a graph chart with the cumulative % of each group. If you go back to the first screen shot I put for the "Associations" in the row 201901 (subscription date YYYYMM) I should have a first line with the first point at 20.56%, the second at 20.56+13.26=33.82%... and so on. A second line for the association which subscribe on the 201902... etc.
Is that clear ? Because I didn't find how to apply your formula to my case.
Hi @Anonymous ,
From your description, you can change the measure like DAX below assuming you have created the [Percentage %] already .
Measure1 = CALCULATE(SUM(Associations[Percentage %]),FILTER(ALLSELECTED(Associations), YEAR(Associations[subscription date] )=YEAR(MAX(Associations[subscription date]))&&MONTH(Associations[subscription date] )=MONTH(MAX(Associations[subscription date]))&&Associations[Temps d'activation]<=MAX(Associations[Temps d'activation])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ok thanks @v-xicai !
so thanks to this link also I understand how to make a cumulative measure : https://www.daxpatterns.com/cumulative-total/
The issue here is that the % is a measure itself is not a column so I cannot use SUM here.
I build the matrix by using my date table as ROWS, the column (calculated with a string in it) is name "Temps d'activation" and the values is a COUTA with a FILTER (so using CALCULATE as a parent) named "# Cohortes Inscriptions". Then I selected the % of row total option for the values.
So i don't know how to adapt.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |