Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Power BI Community,
I would like to know if it is possible to create groups in Power BI with ponders for some values, for example I have database of 3 different products (which are in fact the same but with different quantities):
1xProduct 1
5xProduct 1
50x Product 1
I have data for sales and stock and now I woul like to join this in one product. So I need to multiply sales and stock data for product 50x Product 1 with 50 and for prduct 5x Product 1 with 5 and group it in one product.
I already tried this with power query modification but the loading time increased too much so I need to delete it. If approach above is not possible I would kindly ask for some other ideas.
Thank you,
Jure
Solved! Go to Solution.
Hi @JureGa ,
We can use the split function to meet your requirement.
1. In Editor Query, split the Product column by “x”.
2. Then we need to group the product.1 and product.2.
3. At last, we can create a measure to get the result.
Measure 2 =
SUMX('New table',CALCULATE(SUM('New table'[Product.1]))*CALCULATE(SUM('New table'[Sum_quantity])))
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @JureGa ,
We can use the split function to meet your requirement.
1. In Editor Query, split the Product column by “x”.
2. Then we need to group the product.1 and product.2.
3. At last, we can create a measure to get the result.
Measure 2 =
SUMX('New table',CALCULATE(SUM('New table'[Product.1]))*CALCULATE(SUM('New table'[Sum_quantity])))
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @v-zhenbw-msft, thank you for your reply.
You understood what I want to acchieve, result is correct but is it possible to make adjustment of the existing column "Product" and "Quantity". So that you do not create new column but manipulate existing one.
Thank you!
Jure
Hi @JureGa ,
We can create a measure to meet your requirement.
Measure =
SUMX(
SUMMARIZE(
'Table','Table'[Product],"column1",
SWITCH(
TRUE(),
CONTAINSSTRING(MAX('Table'[Product]),"50x")=TRUE(),SUM('Table'[Quantity])*50,
CONTAINSSTRING(MAX('Table'[Product]),"5x")=TRUE(),SUM('Table'[Quantity])*5,
CONTAINSSTRING(MAX('Table'[Product]),"1x")=TRUE(),SUM('Table'[Quantity]))),
[column1])
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @amitchandak,
my file is really big and has a lot of sensitive data, so maybe I can explain you what I want to acchieve with the tables bellow:
1. I have data source like this
Product | Quantity |
1xProduct 1 | 1 |
1xProduct 1 | 3 |
1xProduct 1 | 1 |
5xProduct 1 | 2 |
5xProduct 1 | 1 |
50xProduct 1 | 2 |
1xProduct 1 | 2 |
1xProduct 1 | 2 |
1xProduct 1 | 2 |
50xProduct 1 | 2 |
2. When I run Power BI report the following data is shown:
Product | Quantity |
1xProduct 1 | 11 |
5xProduct 1 | 3 |
50xProduct 1 | 4 |
3. But I want to achieve this:
Product | Quantity |
1xProduct 1 | 226 |
Is it understandable?
Thank you and BR,
Jure
@JureGa ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |