Plotting Curves by Group

[Sweetcapitan]

Hi all,
I am puzzled with a task that is easy to do in excel but I don’t know how to do it in power BI and need your help. Basically I have 3 fields X, Y, Z in a table and I want to plot Y against X group by Z. Say there are 5 distinct values of Z, then thd resulting visual would have 5 curves of Y against X. The visual will also reflect all slicers placed on the same tab. Any tips? Thanks!

[Phil_Seamark]

Sure, any chance of some sample data + a screenshot showing how it would look if you used Excel.  This will make it easier build (plus help clarify any questions)

[Sweetcapitan]

Thanks Phil!

Here is the data. 

Y is the response so the outcomes are Y (1) or N (0). 

X is time of response. 

Z is the test group. The data here shows 2 groups - A and B.

time	resp	group
X	Y	Z
1	1	A
2	0	A
2	1	A
2	0	A
2	1	A
2	0	A
3	1	A
3	0	A
3	1	A
3	0	A
3	1	A
3	0	A
3	1	A
3	0	A
1	1	B
1	0	B
1	1	B
1	0	B
2	1	B
2	0	B
2	1	B
2	1	B
2	1	B
2	1	B
3	1	B
3	1	B
3	1	B
3	1	B
3	1	B
3	1	B
3	1	B

in Excel I would create a pivot table to summarize the results to get two series (A and B) of responses against time (table 1).  Then I would cumulate it as table 2 and feed it to create the final chart, which is the visual I would like to create in Power BI.  Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Sweetcapitan,

Please create a measure like below:

Running total =
CALCULATE (
    SUM ( 'Plotting Curves'[Y] ),
    FILTER (
        ALLEXCEPT ( 'Plotting Curves', 'Plotting Curves'[Z] ),
        'Plotting Curves'[X] <= MAX ( 'Plotting Curves'[X] )
    )
)

Then, add corresponding fields and above measure into a line chart.

Best regards,

Yuliana Gu

[Sweetcapitan]

Ah ha, I got it!  Thanks a lot @v-yulgu-msft!

Now I encounter another issue.  Since each test is conducted at different time, the length of response curve varies (chart 1).  When I plotted the curve with the help of your answer, I got chart 2 below - basically the curve drop to zero where there is no data at the response date (x axis).  How should I solve this?