Forum Discussion

Marko33's avatar
Marko33
Frequent Visitor
2 years ago
Solved

Redistribute Values from Measure

Hi all,

 

I have a scenario where I´m missing the information about which competitor we have lost a deal.

I want to replace the missing data, with a simple distribution (see table below):

 

Competitive BrandLost DealsDistributionNew Competitive Brand          
Blank1500
Comp130.69
Comp210.23
Comp310.23

- Competitive brand is a column in a exisitng table

- Lost Deals is a measure

 

Anybody knows how to create the columns "Distribution" and "New Competitive Brand"?     

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Marko33 ,

     

    Please try:

     

    First, you need to calculate the total number of lost deals for known competitors excluding the "Blank" category.

    Total Known Lost Deals = 
IF('Table'[Competitive Brand] <> "Blank",
SUMX(
    FILTER('Table', 'Table'[Competitive Brand] <> "Blank"),
    'Table'[Lost Deals]
),
0)

     

    The distribution is calculated based on each competitor is share of the total known lost deals.

    Distribution = 
IF('Table'[Total Known Lost Deals] = 0,
0,
DIVIDE(
    'Table'[Lost Deals],
    [Total Known Lost Deals]
))

     

    Distribute the "Blank" lost deals according to Distribution.

    New Competitive Brand =
VAR a = CALCULATE(SUM('Table'[Lost Deals]),FILTER(ALL('Table'),'Table'[Competitive Brand] = "Blank"))
RETURN [Distribution] * a

     

    The final page effect is as follows:

     

    If you have any further questions please feel free to contact me.

     

    pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Marko33 ,

     

    Please try:

     

    First, you need to calculate the total number of lost deals for known competitors excluding the "Blank" category.

    Total Known Lost Deals = 
IF('Table'[Competitive Brand] <> "Blank",
SUMX(
    FILTER('Table', 'Table'[Competitive Brand] <> "Blank"),
    'Table'[Lost Deals]
),
0)

     

    The distribution is calculated based on each competitor is share of the total known lost deals.

    Distribution = 
IF('Table'[Total Known Lost Deals] = 0,
0,
DIVIDE(
    'Table'[Lost Deals],
    [Total Known Lost Deals]
))

     

    Distribute the "Blank" lost deals according to Distribution.

    New Competitive Brand =
VAR a = CALCULATE(SUM('Table'[Lost Deals]),FILTER(ALL('Table'),'Table'[Competitive Brand] = "Blank"))
RETURN [Distribution] * a

     

    The final page effect is as follows:

     

    If you have any further questions please feel free to contact me.

     

    pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!