Simple Tables

[Jesswess]

Hello BI community 

I need help creating 2 tables a report I'm creating 

For context: 
I have a column called WIN/LOSE which is linked to another column called TAT  

If TAT has a value of 4 or less, the cell value returns a WIN  

If TAT has a value of 5 or more, the cell value returns a LOSE 
Here's an example:  

To my point: 

I'm trying to replicate 2 very basic tables in my report. I'm including my data input for you to play with

First, there's a pivot table showcasing count of Unique ID by WIN/LOSE and Month

Then I have a table with 2 formulas.

I'm using the Table visuals to reproduce both my tables exactly how they are in excel and struggling with the following: 

-> Creating the same columns (How can I isolate my WIN's from my LOSE's so that i can apply my formulas)

This is how far I've gotten 😞

Please help 😪

[Anonymous · Accepted solution]

Hi @Jesswess ,

I suggest you to add a [Monthsort] column in your table in Power Query Editor to sort your [Month] column.

For reference: Sort one column by another column in Power BI - Power BI | Microsoft Learn

The first visual you can try matrix chart to achieve your goal.

The second visual you can try measures.

Monthly TAT =
VAR _Count =
    CALCULATE ( COUNT ( 'Table'[UNIQUE ID] ), 'Table'[WIN/LOSE] = "WIN" )
VAR _Total =
    CALCULATE (
        COUNT ( 'Table'[UNIQUE ID] ),
        ALLEXCEPT ( 'Table', 'Table'[MONTH] )
    )
RETURN
    DIVIDE ( _Count, _Total )

Cumulative TAT =
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[UNIQUE ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WIN/LOSE] = "WIN" )
    ),
    CALCULATE ( COUNT ( 'Table'[UNIQUE ID] ), ALL ( 'Table' ) )
)

Result is as below.

Best Regards,
Rico Zhou

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