Need help regarding DAX

[Emranit]

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=11...

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

[v-pagayam-msft · Accepted solution]

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]

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

[v-pagayam-msft]

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]

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).

[Emranit]

Please provide ma a DAX that always divided with Dyelot quantity and Machine selection quantity as the attached image.

There are marked with a box in the image, It's good. It''s count dyelot quantity and selection number Machine. Now my needs a dax that will be the divided output like 3.5. Please help me.

[v-pagayam-msft · Accepted solution]

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.

[Emranit]

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

[v-pagayam-msft · Accepted solution]

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.

[lbendlin]

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" ?