Need help with creating repeat outlet from data universe

[AmeenVanakar]

Hi,

I need help in creating a matrix table in power BI that looks like the one below. Here is the link to .pbix file Test_Repeat.pbix

Universe= all unique outlet

repeat outlet = repeat orders by outlets within each channel

Repeat % = Repeat outlet/universe

Thanks.

[shafiz_p · Accepted solution]

Hi @AmeenVanakar  Try below measures:

Universe = 
SUMX(
    VALUES('Sheet1'[Channel]),
    CALCULATE(DISTINCTCOUNT('Sheet1'[Outlet]))
)

RepeatOutlet = 
SUMX(
    VALUES('Sheet1'[Channel]),
    CALCULATE(
        DISTINCTCOUNT('Sheet1'[Outlet]),
        FILTER(
            VALUES('Sheet1'[Outlet]),
            CALCULATE(COUNTROWS('Sheet1')) > 1
        )
    )
)

RepeatPercentage = DIVIDE([RepeatOutlet], [Universe], 0)

Output:

Check out the file attached here.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,

Shahariar Hafiz

[AmeenVanakar]

Thank you for the help @shafiz_p & @bhanu_gautam 

[bhanu_gautam]

@AmeenVanakar Please find the attached file

DAX

Universe = DISTINCTCOUNT(Sheet1[Outlet])

RepeatOutlet =

CALCULATE(

    DISTINCTCOUNT(Sheet1[Outlet]),

    FILTER(

        Sheet1,

        COUNTROWS(FILTER(Sheet1, Sheet1[Outlet] = EARLIER(Sheet1[Outlet]))) > 1

    )

)

RepeatPercentage =

DIVIDE([RepeatOutlet], [Universe], 0)