Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate percent based on multiple columns

Hello,

 

Is it possible to calculate percentage when MARA-MTART, LOCATIONTYPE and LOCATIONID are the same - like the example below.

So adding an extra column calculating the percentage where the three first columns have the same output.

 

I hope you can help - thank you! 🙂

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    IF the "Mat Nr Count" is a measure, you can modify M like this.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    M =
    VAR _CURRENTDATA =
        CALCULATE ( SUM ( 'dummy data'[Mat Nr Count] ) )
    VAR _Total =
        SUMX (
            FILTER (
                ALL ( 'dummy data' ),
                'dummy data'[MARA-MTART] = MAX ( 'dummy data'[MARA-MTART] )
                    && 'dummy data'[LOCATIONTYPE] = MAX ( 'dummy data'[LOCATIONTYPE] )
                    && 'dummy data'[LOCATIONID] = MAX ( 'dummy data'[LOCATIONID] )
            ),
            'dummy data'[Mat Nr Count]
        )
    RETURN
        DIVIDE ( _CURRENTDATA, _Total )

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I can see your solutions works perfect on the dummy data - very impressive!
      I do have one question as I've made a mistake when I did the dummy pbix file.

      The "Mat Nr Count" is a calculated measure:

      Mat Nr Count= SUMX (
      VALUES ( MODELVIEW[LOCATIONID] ),
      CALCULATE ( DISTINCTCOUNT ( MODELVIEW[MATNR] ) )
      )
       
      Is it possible to do the same but with this as the "Mat Nr Count" measure?

      Once again: Thank you so much! 🙂
      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

        IF the "Mat Nr Count" is a measure, you can modify M like this.

         

        Best Regards,
        Community Support Team _ kalyj

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    Can you try the following Measure:

    M =
DIVIDE (
    CALCULATE ( DISTINCTCOUNT ( TableName[MatNr] ) ),
    SUMX (
        ADDCOLUMNS (
            CALCULATETABLE (
                TableName,
                ALLEXCEPT (
                    TableName,
                    TableName[MARA-MTART],
                    TableName[LOCATIONTYPE],
                    TableName[LOCATIONID]
                )
            ),
            "DC", CALCULATE ( DISTINCTCOUNT ( TableName[MatNr] ) )
        ),
        [DC]
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Fowmy,

       

      Thank you for your response. Unfortunately it doesn't calculate correctly.

      The "M" should calculate like "Frequency" in this example as I've put in my filters based on the three first columns:

       

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        Anonymous 

        Create a sample Power BI file with dummy data and share the link here. You can save in Google drive.