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


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

  • Emranit's avatar
    Emranit
    Helper II
    1 year ago

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.