Forum Discussion

AmeenVanakar's avatar
AmeenVanakar
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Need help with creating repeat outlet from data universe

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.

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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)
  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

    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