Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Distinct Orderid group by profile

If you take Idorder FGY for example, I have two different id's c475 and 481e with same element.What I want is distintc ids and group by pro in the expected out below:

 

Sample Data

idsPricetotalProElementIdorder
c4756991398AdxibleFGY
c4756991398AdxibleFGY
481e6991398AdxibleFGY
481e6991398AdxibleFGY
63463692775AdexibleDMF
63463692775AdNonexibleDMF
ecdo3692775AdexibleDMF
ecdo3692775AdNonexibleDMF
4f105562775AdexibleDMF
4f105562775AdNonexibleDMF
efef5562775AdexibleDMF
efef5562775AdNonexibleDMF
7f365562775AdexibleDMF
7f365562775AdNonexibleDMF
o4dy3692775AdexibleDMF
o4dy3692775AdNonexibleDMF
a12b0219dentNonexibleTHN
a12b0219dentNonexibleTHN
a12b219219dentNonexibleTHN
a12b219219dentNonexibleTHN
CB65138414Adexible77LL
CB65138414Adexible77LL
C7DC69414chiexible77LL
C7DC69414chiexible77LL
220F69414chiexible77LL
220F69414chiexible77LL
D88B138414Adexible77LL
D88B138414Adexible77LL
20320205Adexible9MA
20320205Adexible9MA
2032205205Adexible9MA
2032205205Adexible9MA


Expected Sample Table Report in PowerBI:

ProelemtidTotal
ADexible77LL276
Chiexible77LL138
Adexible9MA205
ADexibleDMF   1,688.00
ADNonexibleDMF   1,107.00
AdxibleFGY699
AdxibleFGY699
dentNonexibleTHN219
Need Help

6 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    If you place all these columns in a table visual it will apply group by as default, have you tried that?

     

     
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Mariusz if i drag the columns in a Table visual, It is not giving me what I want 100%, please see below the result when I put all these columns in a table visual:

       

      If you take ID: "DMF for example has 12 rows, 2 disticnt elemt (exible and non exible),6 unique ids but belong same pro categories, if you remove the duplicated ids, the result should be as below for DMF, 

      ADexibleDMF             1,688.00
      ADNonexibleDMF             1,107.00

       

      The Expected result is below :

      ProelemtidTotal
      ADexible77LL276
      Chiexible77LL138
      Adexible9MA205
      ADexibleDMF             1,688.00
      ADNonexibleDMF             1,107.00
      AdxibleFGY699
      AdxibleFGY699
      dentNonexibleTHN219

       

      Please see attached powerBI file with sample data 

      powerBI file attached 

      I hope this explanation helps

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        6 years ago

        Anonymous ,

         

        Could you please clarify the step of “remove the duplicated ids” and show the logic of achieving 1,688.00 and 1,107.00?

         

        Regards,

        Jimmy Tao

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

    If you take Idorder FGY for example, I have two different id's c475 and 481e with same element.What I want is distintc ids and group by pro in the expected out below:

    Could you please clarify more details about the logic of grouping by? In addtion, are 'Ad' and 'AD' same in the Pro column?

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-yuta-msft  ad and AD are in the same pro column was just  a typo with caps

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Measure =
        VAR SumTrip = SUMMARIZE('Table (2)','Table (2)'[Elemt],'Table (2)'[ids])
        RETURN
        SUMX(SumTrip,MAX('Table (2)'[Price]))

        The measure above almost gave me what I want but the highlited ID and element measure total is wrong and what I expect is :

        ADexibleDMF             1,688.00
        ADNonexibleDMF             1,107.00

         

        I need help fixing the measure and I have attached a link to the PowerBI file

        Pbix file attached 

         

        v-yuta-msft Mariusz