Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Cumulative percentages in a line chart

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.

 

 

MatrixMatrix

 

 

Simple line chart w/o cumulative %Simple line chart w/o cumulative %

 

4 REPLIES 4
v-xicai
Community Support
Community Support

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.

 

 

Anonymous
Not applicable

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 .

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

 

Anonymous
Not applicable

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.
Matrix_option.png


So i don't know how to adapt.

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.