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.
Hi there, this is my problem: I want to create a chart with multiple cumulative lines, based on a non-cumulative sourcetable. The quite simple sourcetable has only 3 columns, date, article and turnover. Date is obvious, article is a unique name (an ID you could say) and turnover is given per date, per article. There are 4 unique articles in the article-column and the chart should show the cumulative turnover per article plus the cumulative turnover in total (so 5 lines in total). Date on the X-axis.
To calculate the (increasing) cumulative turnover per date, I defined a Measure using this DAX-formule:
Cumulative_turnover = CALCULATE(SUM(Sourcetable[Turnover]);FILTER(ALL(Sourcetable);Sourcetable[Date]<=MAX(Sourcetable[Date])))
The Measure works fine and is able to generate the correct line representing the cumulative turnover in total in my line chart. But how do I get the lines for the 4 individual articles in the same chart?
Thank you!
Solved! Go to Solution.
@Coen_Thönissen , try like, put articles on legend
Cumulative_turnover = CALCULATE(SUM(Sourcetable[Turnover]);FILTER(ALL(Sourcetable);Sourcetable[Date]<=MAX(Sourcetable[Date]) && Sourcetable[articles ]=MAX(Sourcetable[articles ])))
or
Cumulative_turnover = CALCULATE(SUM(Sourcetable[Turnover]);FILTER(ALLselected(Sourcetable);Sourcetable[Date]<=MAX(Sourcetable[Date])))
@Coen_Thönissen , try like, put articles on legend
Cumulative_turnover = CALCULATE(SUM(Sourcetable[Turnover]);FILTER(ALL(Sourcetable);Sourcetable[Date]<=MAX(Sourcetable[Date]) && Sourcetable[articles ]=MAX(Sourcetable[articles ])))
or
Cumulative_turnover = CALCULATE(SUM(Sourcetable[Turnover]);FILTER(ALLselected(Sourcetable);Sourcetable[Date]<=MAX(Sourcetable[Date])))
Hi Amitchandak,
Thank you for the two solutions. The second one works fine, great!
The first solution does not work, however. It returns lines per article, but the lines are incorrect. As time (date) increases from the left to the right on the X-axis, the graph plots a dot for every day with turnover. The amount it plots is the cumulative amount of turnover up to that date (sum of all articles). This dot is ‘given’ then to every article with some turnover on that specific date and is not given to articles with no turnover on that specific date. In other words: it results in dots for every day on the X-axis with some turnover, but the dots are shared only by the articles with turnover on that specific date. Lines are then drawn per article, connecting the dots they have a share in. I hope you inderstand what I mean.
But again, the second solution works fine.
Kind regards, Coen Thönissen
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |