Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count and divide based on condition for MTD table

Hi Team,

I have a tbale like below 

Vertical Account DateStatus
InsuranceFB07-07-2022Yes
InsuranceFB09-07-2022Yes
InsuranceFB11-07-2022No
InsuranceInst07-07-2022No
InsuranceInst09-07-2022No
InsuranceInst11-07-2022Yes
InsuranceInst15-07-2022Yes
SalesFB07-07-2022No
SalesFB09-07-2022Yes
SalesFB11-07-2022No
SalesInst07-07-2022Yes
SalesInst09-07-2022Yes
SalesInst11-07-2022Yes
TrasnportFB07-07-2022Yes
TrasnportFB09-07-2022Yes
TrasnportFB11-07-2022No
TrasnportInst07-07-2022No
TrasnportInst09-07-2022Yes
TrasnportInst11-07-2022No
TransportWA15-07-2022Yes

 

My requirment is I have count each account under each vertical and should find out how many "Yes" from status column and should divide from Number of rows of each account and each vartical.

Below is my requirment.

VerticalAccountValueFormula for understaing 
InsuranceFB66.66(Number of "Yes"/Number of Rows) (2/3)
Insurance Inst50(Number of "Yes"/Number of Rows) (2/4)
SalesFB33.33 
Sales Inst100 
TransportFB66.66 
TransportInst33.33 
TransportWA100 

 

Thanks in Advance 

Need Help
  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Create three new queries within Power Query:

     

    First, a new query (let's call it 'Query1') which references your original table and removes all columns apart from the Vertical and Account columns. Then use the Group By feature, Advanced, selecting the Vertical and Account columns and selecting Count Rows as the operation for the new column.

     

    Second, a new query (let's call it 'Query2') which is identical to the above though which contains an additional step prior to the Group By step in which the Status column is filtered for 'Yes' only.

     

    Finally, a new query which performs two merges:

    1) A Left-Outer merge with your original table and Query1, using the Vertical and Account columns. Then expand this table, selecting the Count column only

    2) A Left-Outer merge with the table generated in 1) above and Query2, using the Vertical and Account columns. Then expand this table, selecting the Count column only.

     

    Finally, add a new custom column to this table which performs a simple division of the relevant columns.

     

     

7 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    MyMeasure =
VAR TotalEntries =
    COUNTROWS( 'Table' )
VAR StatusYes =
    CALCULATE( COUNTROWS( 'Table' ), 'Table'[Status] = "Yes" )
RETURN
    DIVIDE( StatusYes, TotalEntries )

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Jos_Woolley ,

      Thanks for the replay .

      I want to create as a table beacause I need to apped this output table with other table.

      Can yu please help me on that.

       

      Thanks in advance !

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    You mean you want a Calculated Column instead of a Measure?

     

    Column =
VAR ThisVertical = 'Table'[Vertical]
VAR ThisAccount = 'Table'[Account]
VAR TotalEntries =
    CALCULATE(
        COUNTROWS( 'Table' ),
        FILTER(
            'Table',
            'Table'[Vertical] = ThisVertical
                && 'Table'[Account] = ThisAccount
        )
    )
VAR StatusYes =
    CALCULATE(
        COUNTROWS( 'Table' ),
        FILTER(
            'Table',
            'Table'[Status] = "Yes"
                && 'Table'[Vertical] = ThisVertical
                && 'Table'[Account] = ThisAccount
        )
    )
RETURN
    DIVIDE( StatusYes, TotalEntries )

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Jos_Woolley 

       

      Thanks for the reply!

      I got the results as expected but I can not use calculted colum to append the query.

      Calculated column is not visible in Power Query.

      Is there way to wite same in Custom Column ?

       

      Thanks !

      • Jos_Woolley's avatar
        Jos_Woolley
        Icon for Solution Sage rankSolution Sage
        4 years ago

        Create three new queries within Power Query:

         

        First, a new query (let's call it 'Query1') which references your original table and removes all columns apart from the Vertical and Account columns. Then use the Group By feature, Advanced, selecting the Vertical and Account columns and selecting Count Rows as the operation for the new column.

         

        Second, a new query (let's call it 'Query2') which is identical to the above though which contains an additional step prior to the Group By step in which the Status column is filtered for 'Yes' only.

         

        Finally, a new query which performs two merges:

        1) A Left-Outer merge with your original table and Query1, using the Vertical and Account columns. Then expand this table, selecting the Count column only

        2) A Left-Outer merge with the table generated in 1) above and Query2, using the Vertical and Account columns. Then expand this table, selecting the Count column only.

         

        Finally, add a new custom column to this table which performs a simple division of the relevant columns.