Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
I have a table like this:
and I would like to have a grouping like this:
As you can the column called "UNIT_QTY" gets the correct values but the subtotal and the total are not correct, in fact my "UNIT_QTY" column should contains the values in "Average of QTY" column which, for the subtotal and total, contains the summatory of the average above.
In order to fix the problem I did something with this measure: SUMX(DISTINCT(TABLE[QTY]),TABLE[QTY]) but obviously it misses the duplicated vales in the calculation; in fact in the second section with AAAA==>PHONE==> I will have (76-38) instead of (38+38).
Please, could you help me on solving this issue?
Thanks
Solved! Go to Solution.
Hi,
Please try these measures:
UNIT_QTY =
SUMX (
GROUPBY (
'Table',
'Table'[CHANNEL],
'Table'[SERVICE],
'Table'[MODEL],
'Table'[CODE]
),
CALCULATE ( AVERAGE ( 'Table'[QTY] ) )
)
AVG PRICE =
SUMX (
GROUPBY (
'Table',
'Table'[CHANNEL],
'Table'[SERVICE],
'Table'[MODEL],
'Table'[CODE]
),
CALCULATE ( AVERAGE ( 'Table'[PRICE] ) )
)
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Try like
AverageX(summarize(Table,table[Channel],table[Service],Table[Model],table[Code],"_1",Sum(Table[Qty])),[_1])
Remove/Add Group by in summarize as per need
Thanks for your quick reply.
I don't understand this "_1" in your formula: AverageX(summarize(Table,table[Channel],table[Service],Table[Model],table[Code],"_1",Sum(Table[Qty])),[_1])
Please could you explain?
If I would like to have this grouping (see values in column E) can I use your previous formula?
Thanks
Hi,
Please try these measures:
UNIT_QTY =
SUMX (
GROUPBY (
'Table',
'Table'[CHANNEL],
'Table'[SERVICE],
'Table'[MODEL],
'Table'[CODE]
),
CALCULATE ( AVERAGE ( 'Table'[QTY] ) )
)
AVG PRICE =
SUMX (
GROUPBY (
'Table',
'Table'[CHANNEL],
'Table'[SERVICE],
'Table'[MODEL],
'Table'[CODE]
),
CALCULATE ( AVERAGE ( 'Table'[PRICE] ) )
)
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Thanks for your feedback!
I have replaced the function GROUPBY with SUMMARIZE to be used in my PowerPivot table and it works
I'm also trying to get the same result by creating a KEY field and for each unique KEY I have to add the PRICE...
I'm new on this DAX functions...
So I'm looking for something like: "For each unique KEY take the PRICE"
Thanks a lot for your great support!
Hi,
You can still use GROUPBY function in Excel PowerPivot, see this:
Best Regards,
Giotto
Thanks for your example.
This is what happens to me when I add GROUPBY function in my PowerPivot:
Thanks
Hi,
It is related to the Excel version, please try to update your excel to the latest version.
Best Regards,
Giotto
@ v-gizhi-msft
I have Excel 2013; do I have to install 2016 version?
About GROUPBY function I have replaced the function GROUPBY with SUMMARIZE; is it the same?
It has the same effect on my report?
Thanks
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |