Profit rate on a daily basis

[Dart1]

Hi everyone,

Newbie here and need help. I have a data of dates and profits/losses. I need to get a rate of profits to losses based on end of day result. Below is an example of my data. i have several inputs for the same dates and have a result for each day under daily result column. My result rate is 67% overall (two positive daily results and 1 negative result).

How can I achieve thie result rate with power BI?

Thanks in advance!

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.

[Dart1]

Here you go:

Date	Sales
11/2/2022	10
11/2/2022	20
11/2/2022	-50
11/2/2022	30
11/2/2022	-60
11/2/2022	15
11/2/2022	22
11/2/2022	37
11/3/2022	56
11/3/2022	-69
11/3/2022	-85
11/3/2022	55
11/3/2022	-90
11/3/2022	100
11/3/2022	-200
11/3/2022	150
11/4/2022	-120
11/4/2022	130
11/4/2022	45
11/4/2022	-50
11/4/2022	65
11/4/2022	72
11/4/2022	100
11/4/2022	-85
11/4/2022	15

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Dart1]

Thank you so much! Exactly what I was looking for!

One more question if this is not too much to ask:

Some of my data has also time in it and when I check those data then there seems to be an issue with the results.

Below is an example for those dates and not sure how to make this work? Thanks again for your time!

Also, I'm using a slicer that is usign the main date data as slicer input and when I change the date from slicer then the ratio doesn't change. Maybe due to using the new table date? How can I fix this?

1/3/2023 7:53:45 AM

1/3/2023 11:01:23 AM

1/3/2023 11:12:43 AM

1/3/2023 12:07:20 PM

1/4/2023 6:44:37 AM

1/4/2023 6:53:22 AM

1/4/2023 7:55:56 AM

1/4/2023 8:04:37 AM

1/4/2023 8:19:57 AM

1/4/2023 9:02:55 AM

1/4/2023 9:30:33 AM

1/4/2023 12:08:22 PM

1/5/2023 6:59:48 AM

1/5/2023 8:33:35 AM

1/5/2023 10:23:01 AM

1/5/2023 11:17:44 AM

1/5/2023 11:56:11 AM

1/6/2023 7:35:23 AM

1/6/2023 8:59:34 AM

1/6/2023 9:59:03 AM

1/6/2023 10:51:19 AM

1/6/2023 11:08:23 AM

1/6/2023 11:08:56 AM

1/6/2023 12:51:26 PM

1/9/2023 6:58:49 AM

1/9/2023 7:58:12 AM

1/9/2023 8:17:59 AM

1/9/2023 9:07:51 AM

1/9/2023 10:30:34 AM

1/9/2023 10:35:40 AM

1/9/2023 11:30:57 AM

1/10/2023 6:21:37 AM

1/10/2023 6:34:31 AM

1/10/2023 7:14:46 AM

1/10/2023 7:15:00 AM

1/10/2023 8:13:37 AM

1/10/2023 8:22:29 AM

1/10/2023 8:22:46 AM

1/10/2023 10:16:30 AM

1/10/2023 10:51:18 AM

[Ashish_Mathur]

You are welcome.  In the Query Editor, change the datetype to date.

[grantsamborn]

Hi @Dart1 

Maybe try this in a card.

z = 
DIVIDE(
    COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                VALUES( 'DataTable'[Date] ),
                "_Sales", CALCULATE( SUM( 'DataTable'[Sales] ) )
            ),
            [_Sales] > 0
        )
    ),
    DISTINCTCOUNT( 'DataTable'[Date] )
)

[smpa01]

@Dart1  you need a measure as following

Measure = CALCULATE(SUM(tbl[sale]),ALLEXCEPT(tbl,tbl[date]))

[Dart1]

Thanks for your time! 

I added the above measure to my table and the result of this measure is as below.   Not really representing the 67% rate. Maybe I need to try to expain better on my end. I have three days above and every day has several results in it.. At the end of the day, each day has a profit or loss result. In the table above I have 2 profit days and 1 loss day. This brings a 67% daily rate.  Power BI needs to calculate every individial day and come up with a result for each day. Then needs to calculate the rate of profit resulted days' count to the overall different days count.