Forum Discussion

Jesswess's avatar
Jesswess
New Member
3 years ago
Solved

Simple Tables

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 ðŸ˜ª

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.