Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

conditional divide

Hi all,   I need to calculate WAP by divding Sales by quantity only for columns which has been categorized as priced. The quantity under unpriced should not be added. In the WAP column below I hav...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous - It looks like you're creating WAP as a Calculated Column. It needs to be a Measure.  Also, Numerator and Denominator need to be Measures.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello,

  1. Please see the interface of my report which I want to achieve.The price/unpriced filter should give WAP only for the quantities which has been Priced. the top-right chart should accordingly show top customers by WAP which has been "priced"
  2. Please find enclosed the cleaned data excel , for the report above. Col D of the excel tags each entry as  priced or unpriced. Please Note Col A for customers. Here, Some quantities for a particular customer (ex. Bai, Col, Ide) is priced while some are unpriced. In such cases the WAP in the final table should calculate WAP only priced quantities (899,1676,1254 respectively). For customers for which every thing is priced (ex. Ken, Kon) it should calculate for total sales/ total quantity (704, 947 respectively). Please , see the desired WAP in Col "I'.

I am not able to attach the working excel. Is it possible to share files. it would be great if you can tell me how to? Meanwhile please see a snaphot of the excel below.

 

Please let me know if you need more info.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous - The solution that v-frfei-msft  provided looks like it should work for your requirement. One thing you could try is to split out the numerator and denominator into 2 additional measures for testing:

 

WAP Numerator =
    CALCULATE (
        SUM ( 'Master Pricing Data'[Sales] ),
        FILTER ( 'Master Pricing Data', 'Master Pricing Data'[Priced/Unpriced] = "Priced" )
    )


WAP Denominator = 
    CALCULATE (
        SUM ( 'Master Pricing Data'[Quantity] ),
        FILTER ( 'Master Pricing Data', 'Master Pricing Data'[Priced/Unpriced] = "Priced" )
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    It worked for those entries which have one priced and the other unpriced, like this one.

     

    However , those which have more than one priced , its incorrect, beause its adding up individual WAP

    In secind case WAP should 899. However , its adding up 1250 and 808 to give 2058 as the result. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous - That calculation should work, so we need to see the text of your calculation and a screenshot of the wrong result in a Power BI visual. You may need to let us take a look at your pbix - you can upload it to dropbox or onedrive. Of course, protect your data.

      Cheers!

      Nathan

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks Nathan, 

        Here are the visuals

         

         

        Thw incoreect result on Power BI

         

        The formula used

         

        I will work on sharingthe PBIX.

         

        Thanks