Forum Discussion

Emranit's avatar
Emranit
Helper II
1 year ago
Solved

Need help regarding DAX

In my attached excel file,

1] 500kg machine have 03pcs, 750kg machine have 04pcs, 1000kg machine have 02pcs, 1200kg machine have 01pcs, 1500kg machine have 01pcs

2] Total Dyelot no quantity is 11

I want to divided total dyelot no quantity with total 500kg machine quantity(03).

Thus, divided total dyelot no quantity with total 750kg machine quantity(04),
divided total dyelot no quantity with total 1000kg machine quantity(02),
divided total dyelot no quantity with total 1200kg machine quantity(01),
divided total dyelot no quantity with total 1500kg machine quantity(01),

 

3] Then divided result 3.66 will be place top of the bar of 500kg machine,

divided result 2.75 will be place top of the bar of 750kg machine,
divided result 5.5 will be place top of the bar of 1000kg machine,
divided result 11 will be place top of the bar of 1200kg machine,
divided result 11 will be place top of the bar of 1500kg machine,

 

please give me a DAX like the attached picture to the attached BI file.

 

https://drive.google.com/file/d/1qMA_GVjtrrstwQ7hfB5keS77m71ETdGf/view?usp=drive_link

 

https://docs.google.com/spreadsheets/d/1hIKiLCKYTjZoYn0eeugDiAYENvMiopU0/edit?usp=drive_link&ouid=113914284006601104314&rtpof=true&sd=true

 

https://drive.google.com/file/d/1F4Cy1u-6BpKWDiHW7lVdk2cjp5ccKSD7/view?usp=drive_link

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Emranit ,
    Thank you for reaching out to the Microsoft Fabric Community Forum about the issue you are encountering.

    Based on my understanding, the following DAX measure might help you address the query:

                  Dyelot Per Machine = VAR TotalDyelot = 11 
                  DIVIDE(TotalDyelot, SELECTEDVALUE('Machines'[Machine Count]), BLANK())

    To get the divided result on top of the bar of each value machine, select a clustered column chart :

    • Use Machine Weight (kg) as the X-axis
    • Use Machine Count as the Y-axis

    Display the Dyelot per Machine (new measure) as data labels on top of each bar. To enable data labels:

    • Click on the chart ,select Format then turn on Data Labels
    • Set labels to show Dyelot Per Machine value

    Refer the attached file for more clarification.

    If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Emranit ,

    Thanks for your follow-up! Based on your request, you may use the following DAX formula to calculate the average number of machines by dividing the total selected machines by the unique machine count:

    new Average_Machines =
    VAR TotalMachines = COUNTROWS(ALLSELECTED('Machine'))  
    VAR UniqueMachines = CALCULATE(DISTINCTCOUNT('Machine'[Machine Info]), ALLSELECTED('Machine'))  

    RETURN
    DIVIDE(TotalMachines, UniqueMachines, 0)


    For better understanding, I have created a new measure to get the average value.Please refer the below screenshot for clear understanding.

     

    You can either modify your existing card measure or create a new measure using this formula to get the average as needed. Let me know if you need any further adjustments!

    I hope this helps.Thank you for your cooperation. Have a great day.

    Regards,
    Pallavi.


8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Sorry, your premise is not clear.  Can you explain the business process in more detail?  What is the significance of the "Production"  column? What are "dyelots" ? What is "MC" ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Emranit ,
    Thank you for reaching out to the Microsoft Fabric Community Forum about the issue you are encountering.

    Based on my understanding, the following DAX measure might help you address the query:

                  Dyelot Per Machine = VAR TotalDyelot = 11 
                  DIVIDE(TotalDyelot, SELECTEDVALUE('Machines'[Machine Count]), BLANK())

    To get the divided result on top of the bar of each value machine, select a clustered column chart :

    • Use Machine Weight (kg) as the X-axis
    • Use Machine Count as the Y-axis

    Display the Dyelot per Machine (new measure) as data labels on top of each bar. To enable data labels:

    • Click on the chart ,select Format then turn on Data Labels
    • Set labels to show Dyelot Per Machine value

    Refer the attached file for more clarification.

    If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

  • Emranit's avatar
    Emranit
    Helper II
    1 year ago

    In the attached excel file total dyelot quantity 25 and total machine type of quantity 8.

    I am getting default Dyelot quantity per Machine in BI file when divided with dyelot quantity and Machine type of Quantity. It's good.

    Additionally, my requirements are if I select one machine one will divide with display dyelot quantity that will present in attached BI file in Bar visualization (default Dyelot per Machine).

      Thus, if I select more machines, more machine numbers of quantities will divide with display dyelot quantity that will present in attached BI file in Bar visualization (default Dyelot per Machine).

     

    Thus it will be work by a DAX. Please see the attached files and help me.

     

    https://docs.google.com/spreadsheets/d/17ywpjqENpUQS_qk4Df5VSjVfdcoNEWkI/edit?usp=sharing&ouid=113914284006601104314&rtpof=true&sd=true

     

    https://drive.google.com/file/d/106qZGsrfDdxEwzecL1bVAlHYOw_cWS1-/view?usp=sharing

     

    https://drive.google.com/file/d/19PuLZO_cOpbv_9vp5C6L0EK_9N10npb2/view?usp=sharing

     

     

  • Emranit's avatar
    Emranit
    Helper II
    1 year ago

    In the attached excel file total dyelot quantity 25 and total machine type of quantity 8.

    I am getting default Dyelot quantity per Machine in BI file when divided with dyelot quantity and Machine type of Quantity. It's good.

    Additionally, my requirements are if I select one machine one will divide with display dyelot quantity that will present in attached BI file in Bar visualization (default Dyelot per Machine).

      Thus, if I select more machines, more machine numbers of quantities will divide with display dyelot quantity that will present in attached BI file in Bar visualization (default Dyelot per Machine).

     

    Thus it will be work by a DAX. Please see the attached files and help me.

     

    https://docs.google.com/spreadsheets/d/17ywpjqENpUQS_qk4Df5VSjVfdcoNEWkI/edit?usp=sharing&ouid=11391...

     

    https://drive.google.com/file/d/106qZGsrfDdxEwzecL1bVAlHYOw_cWS1-/view?usp=sharing

     

    https://drive.google.com/file/d/19PuLZO_cOpbv_9vp5C6L0EK_9N10npb2/view?usp=sharing

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Emranit ,

      Upon reviewing the provided information, Based on my understanding below steps might assist you in addressing the issue.


      1. Create a measure for  Dyelot Per Machine Type using below DAX query:  

                Dyelot Per Machine Type =
                VAR TotalDyelot = SUM('Machine'[Dyelots Quantity])  -- Sum of all Dyelot Quantities
                VAR TotalMachineTypes = DISTINCTCOUNT('Machine'[Machine Info])  -- Unique machine types count

                RETURN
               DIVIDE(TotalDyelot, TotalMachineTypes, BLANK())  -- Avoid division by zero
      2.Create a measure for Dyelot Per Selected Machines. This measure ensures that if one or more machines are selected, the calculation updates accordingly.
               Dyelot Per Selected Machines =
               VAR TotalDyelot = SUM('Machine'[Dyelots Quantity])  
               VAR SelectedMachineTypes = CALCULATE(
               DISTINCTCOUNT('Machine'[Machine Info]),
               ALLSELECTED('Machine')  -- Only count selected machine types
               )

              RETURN
              DIVIDE(TotalDyelot, SelectedMachineTypes, BLANK())
      Use the Dyelot Per Selected Machine measure in the bar chart:
      For X-Axis , use Machine Info
      For Y-Axis , use Dyelots Quantity
      As Data Label, use Dyelot Per Selected Machine.

      3.Create a measure using below DAX query then selcet the card visual to get the desired output
               Selected Machine Count =
               VAR TotalMachines = COUNTROWS(ALLSELECTED('Machine'))  -- Total selected rows in the dataset
               VAR UniqueMachines = CALCULATE(DISTINCTCOUNT('Machine'[Machine Info]),                                               ALLSELECTED('Machines'))  -- Unique selected machine types

               RETURN
              FORMAT(TotalMachines, "0") & " / " & FORMAT(UniqueMachines, "0")
      4.To get the exact value instead of closest number:
      • After turning on Data Labels, select Value.
      • Set display units to None.

       

      Attaching the file and the screenshot for your reference.This will help you in clear understanding.

       



      If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

      Thank you for being a valued member of the Microsoft Fabric Community Forum!



      • Emranit's avatar
        Emranit
        Helper II
        1 year ago

        Actually I didn't get my requirements. I'm getting default dyelot quantity from each machine per day. I need not sum of dyelot quantity as your attached images. If I select one or more machine one or more number of quantity will divide with display dyelot quantity that will present in attached BI file in Bar visualization (default Dyelot per Machine).