Count and divide based on condition for MTD table

[raassd]

Hi Team,

I have a tbale like below 

Vertical	Account	Date	Status
Insurance	FB	07-07-2022	Yes
Insurance	FB	09-07-2022	Yes
Insurance	FB	11-07-2022	No
Insurance	Inst	07-07-2022	No
Insurance	Inst	09-07-2022	No
Insurance	Inst	11-07-2022	Yes
Insurance	Inst	15-07-2022	Yes
Sales	FB	07-07-2022	No
Sales	FB	09-07-2022	Yes
Sales	FB	11-07-2022	No
Sales	Inst	07-07-2022	Yes
Sales	Inst	09-07-2022	Yes
Sales	Inst	11-07-2022	Yes
Trasnport	FB	07-07-2022	Yes
Trasnport	FB	09-07-2022	Yes
Trasnport	FB	11-07-2022	No
Trasnport	Inst	07-07-2022	No
Trasnport	Inst	09-07-2022	Yes
Trasnport	Inst	11-07-2022	No
Transport	WA	15-07-2022	Yes

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.

Vertical	Account	Value	Formula for understaing
Insurance	FB	66.66	(Number of "Yes"/Number of Rows) (2/3)
Insurance	Inst	50	(Number of "Yes"/Number of Rows) (2/4)
Sales	FB	33.33
Sales	Inst	100
Transport	FB	66.66
Transport	Inst	33.33
Transport	WA	100

Thanks in Advance 

[Jos_Woolley]

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

[raassd]

@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 · Accepted solution]

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.

[raassd]

@Jos_Woolley 

Thank you alot.

Working as expected !

Cheers

[Jos_Woolley]

Good to know! 

Cheers

[Jos_Woolley]

Hi,

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

Regards

[raassd]

@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 !