Forum Discussion

NickOnTheNet's avatar
NickOnTheNet
Frequent Visitor
5 years ago

Totals based on product groups

Hi all,

 

I need to produce a report with the following results from this (simplified) table, there are only 2 product groups in the real table.

(Each row in the table below represents an item in a sales contract.)

 

Total unique count of contracts: 5 (in Rows 1-2, 3, 4-6, 7-8, 9)

Total amount freight 20 USD

Total amount products 160 USD

 

Total unique count of Contracts and total Amounts for Products and Freight also as a percentage for the following 3 conditions:

 

1.) From rows containing only items from Grp1:

Count=2 (40%), Products=95 USD (59,4%), Freight=0 USD (0%) (in Rows 7-8, 9)

 

2.) From rows containing only items from Grp2:

Count=2 (40%), Products=30 USD (18,8%), Freight=20 USD (100%) (in Rows 3, 4-6)

 

3.) From rows containing items from Grp1 and Grp2 and Freight if given:

Count=1 (20%), Products=35 USD (21,9%), Freight=0 USD (0%) (in Rows 1-2)

 

RowContract NoProduct GroupAmount USD
11234Grp110
21234Grp225
31235Grp215
41236Grp25
51236Grp210
61236Freight20
71237Grp125
81237Grp130
91238Grp140

 

Could someone perhaps either point me to an example which solves a similar problem, offer a solution or at least explain if I need to use DAX? (I am sorry if I have explained the situation above too simply for some as I am unsure exactly which terminology to use to frame it otherwise.)

 

Thank you for your time and consideration.

1 Reply

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support

    Hi NickOnTheNet ,

     

    I can't get your issue well based on your description. What's your expected output? Would you please show us expected output based on your sample data?

     

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

     

    Best Regards,

    Dedmon Dai