The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |