Forum Discussion

Serj's avatar
Serj
Frequent Visitor
4 years ago
Solved

Calculate share based on condition

Hello everyone,

Would you pls have a look at this one.

There is a sample table of product items, their price and availability.

I want to calculate a price share for product items based on their availability.

With Excel, I have done that in 5 minutes (column “%”), but got stuck in PowerBI.

This is Excel formula for available products, if my explanation is not clear enough:

=IF(C2="Y",B2/(SUMIF(C$2:C$9,"Y",B$2:B$9))," ")

 

Item

Price

Available?

%

A

5

Y

18.5%

C

10

Y

37.0%

D

4

N

 

E

2

Y

7.4%

F

6

N

 

G

5

N

 

H

6

Y

22.2%

I

4

Y

14.8%

 

Thanks a lot in advance!

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Serj ，

    Create the below column:

    PER2 =
IF (
    'Table'[Available] = "Y",
    ROUND (
        'Table'[Price]
            / CALCULATE (
                SUM ( 'Table'[Price] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Available] = "Y" )
            ),
        3
    )
)

     

    Outputresult:

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

1 Reply

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Serj ，

    Create the below column:

    PER2 =
IF (
    'Table'[Available] = "Y",
    ROUND (
        'Table'[Price]
            / CALCULATE (
                SUM ( 'Table'[Price] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Available] = "Y" )
            ),
        3
    )
)

     

    Outputresult:

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien