Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add Percentage Values based on External Sales.

Hi Experts

 

How would you amend the current DAX Measure to add percenatge value based on External Sale. into the Measure to Column so the data and percentage values are as the excel file attached.

 

Excel File.

https://www.dropbox.com/scl/fi/pku9r6ojigak7uv1otbiu/Values.xlsx?dl=0&rlkey=2gmprbli0xsw98dbijjbw3roc 

 

Sample PBIX

https://www.dropbox.com/s/t1lbguwygrc5nzn/PLP_Test_2%20%281%29%20%281%29.pbix?dl=0 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Solution to question the % values should take the following forum..

    2020 Full Year =
    VAR _external =
    SUMX (
    FILTER (
    ALL ( Logistics_ ),
    Logistics_[New_Reporting_Headers]="External Sales"
    ),
    [__v_Act2020]
    )
    VAR _Duty =
    SUMX (
    FILTER (
    ALL ( Logistics_ ),
    Logistics_[New_Reporting_Headers]="Duty"
    ),
    [__v_Act2020]
    )
    VAR _DutyPercentage =
    FORMAT(DIVIDE(_duty,_external,0),"0.0%;(0.0%)")
    VAR _Inbound_Freight =
    SUMX (
    FILTER (
    ALL ( Logistics_ ),
    Logistics_[New_Reporting_Headers]=
    "Inbound Freight"
    ),
    [__v_Act2020]
    )
    VAR _InboundPercentage =
    FORMAT(DIVIDE(_Inbound_Freight,_external,0),"0.0%;(0.0%)")
    VAR _Warehousing_Fixed =

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try one of the two measures

     

    divide(calculate([measure 2], filter(Logistics_,Logistics_[new_Reporting_header] ="External sales")),[Measure 2])

     

     

    divide(calculate([measure 2], filter(Logistics_,Logistics_[new_Reporting_header] ="External sales")),calculate([Measure 2], allselected(Logistics_)))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Solution to question the % values should take the following forum..

      2020 Full Year =
      VAR _external =
      SUMX (
      FILTER (
      ALL ( Logistics_ ),
      Logistics_[New_Reporting_Headers]="External Sales"
      ),
      [__v_Act2020]
      )
      VAR _Duty =
      SUMX (
      FILTER (
      ALL ( Logistics_ ),
      Logistics_[New_Reporting_Headers]="Duty"
      ),
      [__v_Act2020]
      )
      VAR _DutyPercentage =
      FORMAT(DIVIDE(_duty,_external,0),"0.0%;(0.0%)")
      VAR _Inbound_Freight =
      SUMX (
      FILTER (
      ALL ( Logistics_ ),
      Logistics_[New_Reporting_Headers]=
      "Inbound Freight"
      ),
      [__v_Act2020]
      )
      VAR _InboundPercentage =
      FORMAT(DIVIDE(_Inbound_Freight,_external,0),"0.0%;(0.0%)")
      VAR _Warehousing_Fixed =
      • v-henryk-mstf's avatar
        v-henryk-mstf
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Good job!😋

         

        Best Regards,

        Henry

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit

      Well this give me Values and % in the same column???

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit - Any thoughts its not giving the values for the percentages???

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Anonymous ,


    Your original link has been deleted, can you re-provide the pbix file link?


    Let me know immediately, looking forward to your reply.


    Best Regards,

    Henry