Forum Discussion

Sankzpower's avatar
Sankzpower
Icon for Helper I rankHelper I
6 years ago
Solved

Multiply 2 columns row then divide by count grouped

Hi guys

 

Hope you can help me out here. Sure, this is simple but again cannot execute cleanly on power bi. 

 

I have sample two tables both are connected via category. Each category has average use in sheet2. All I need is to calculate average use by multiplying the sleep into Avg use present in sheet 2 however its calculated by site. Sample power bi file here

 

The expected result is basically to find out total avg. use per site:

 

SiteAccountCategoryCommon GroupSleepsAvg.UseTotal Avg. Use
1A1C1EE Group125140087500
1A2C1EE Group125140087500
1A3C1GG Group1251500187500
1B4C1EE Group1251400175000
1B5C1EG Group1251400175000
1C6C1GG Group1251500187500

 

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Sankzpower 

    You data is not like the expected result picture.

    Use measure below, result is

    Measure 2 = DIVIDE(
    SUM ( Sheet1[Sleeps] )
        * SUM ( Sheet2[Avg.use] ),CALCULATE(DISTINCTCOUNT(Sheet1[Account]),ALLEXCEPT(Sheet1,Sheet1[Category],Sheet1[Site])))

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Sankzpower 

    Create a measure

    Measure 2 =
DIVIDE (
    SUM ( Sheet1[Sleeps] )
        * SUM ( Sheet1[Avg.use] ),
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        ALLEXCEPT (
            Sheet1,
            Sheet1[Common Group],
            Sheet1[Category],
            Sheet1[Site]
        )
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Sankzpower's avatar
      Sankzpower
      Icon for Helper I rankHelper I
      6 years ago

      Hi v-juanli-msft 

       

      I wanted to mention you that the two tables are seperate but linked via category ID. There was an error in my data source which I previously attached. But power bi file has been updated to reflect current data here . I can confirm the result are the same as expected below.

       

      Can you please check and let me know if  there is a workaround to achive the same result which i needed as below?

       

      Thanks in advance

       

      SiteAccountCategoryCommon GroupSleepsAvg.UseTotal Avg. Use
      1A1C1EE Group125140087500
      1A2C1EE Group125140087500
      1A3C1GG Group1251500187500
      1B4C1EE Group1251400175000
      1B5C1EG Group1251400175000
      1C6C1GG Group1251500187500
      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Sankzpower 

        You data is not like the expected result picture.

        Use measure below, result is

        Measure 2 = DIVIDE(
    SUM ( Sheet1[Sleeps] )
        * SUM ( Sheet2[Avg.use] ),CALCULATE(DISTINCTCOUNT(Sheet1[Account]),ALLEXCEPT(Sheet1,Sheet1[Category],Sheet1[Site])))

         

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.