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 a data set that looks like belwo. Essentially, a count of each category for each day (may not be consecutive days, but it's irrelavant).
Date Type Value
10/19/2021 A 17
10/19/2021 B 18
10/19/2021 C 9
10/19/2021 D 11
10/22/2021 A 15
10/22/2021 B 19
10/22/2021 C 11
10/22/2021 D 5
10/27/2021 A 17
10/27/2021 B 27
10/27/2021 C 4
10/27/2021 D 2
My goal is to plot this in a chart where I want to show the percentage of each type (a percentage of daily total of all 4 Types for the day), for each of the days I have data. For example, if we focus on type D, for October 19th the % should be 20% (11 out of 55), for October 22nd it's 10% (5 out of 50) and for October 27th, it's 4% (2 out of 50). And I want to plot this for all 4 types. Basically, what I want is this matrix in a line chart format:
I get the above matrix by plotting 'Value' as %RT Value in 'Show value as' option of the matrix:
But, if I plot the same data in a line chart, this just would'nt work. This is because I don't have a "Show value as" option that says "% of Row Total". I can only do "Percent of grand total" and it produces a chart like this, which is not what I'm looking for.
I assume that instead I have to calculate measures, presumably a "Daily Total" that gives me the total of A, B, C, and D for each day, and a second measure that gives me the % of each type based on that total. But I'm not sure how I can caluclate that measure. How do I go about it?
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
another way of doing , adding a measure
Perc By Col Total =
var _t = sum ('Table'[Value])
var _d = minx('Table', 'Table'[Date])
var _v = CALCULATE( sum ('Table'[Value]) , FILTER( all('Table'), 'Table'[Date] = _d))
return _t / _v
Hi,
You may download my PBI file from here.
Hope this helps.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |