Classifying values in new column by dates range column?

[Oscartv97]

Hello,

I have the following table:

Is there a possible way to classify the value column in a new one?, for example i know that first dates set (green) are gross profit as percentage of revenue values, and the second set of dates (blue) are net income as percentage of revenue values. Is there a possible way classify them in a new column by these two concepts?

The resultant table should look like this:

 

I know this can be easily done in excel, but i want to keep excel out of the process.

Thank you!

[Anonymous · Accepted solution]

Hello @Oscartv97 ,

Because it has a gross profit value and a net income value on the same date, and the gross value of gross profit is greater than net income, you can classify them by the value of the same date. I also considered the situation of different companies.

Create a calculated column.

Ratio = 
VAR MAXVALUE =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date],'Table'[Companny] ) )
RETURN
    IF (
        [Value] = MAXVALUE,
        "Gross profit as percentage of revenue",
        "Net income as percentage of revenue"
    )

More details can be found here.

Best regards
Stephen Tao

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

[Oscartv97]

@v-stephen-msft Thank your for the soluiton, brilliant!

[Anonymous]

Hi @Oscartv97 ,

Glad to help you, please accept my reply as an answer. Thank.

Best Regards,

Stephen Tao

[Greg_Deckler]

@Oscartv97 - So by what criteria is each row one or the other?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.