Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello friends, please can someone help me?
I need to create a DAX measure that calculates Pareto (80:20) of products. All the code I have written so far has resulted in failure as it exceeds available memory.
In my data model I have a product dimension table related to a sales fact table. The products table has 150 thousand rows and the sales table has 33 million rows. Download an example PBIX here.
I need a DAX measure that calculates the pareto of products, so I will be able to filter all products classified as "A" or "B" or "C". My attempts are taking 10 minutes or sometimes it doesn't load because it exceeds the available memory. I need a measurement or some technique that is quick to calculate.
Solved! Go to Solution.
@Rai_BI - Now that I know you need a static analysis, your best solutuion will be to create a calculated table that does all the calculations in one.
Below is the DAX to create this table, you can change the ABC variable to suit your classifications & remove any unecessary columns from SELECTCOLUMNS() to remove them from the table.
To input this DAX go to Modeling > New Table.
VAR sales_by_prod =
SUMMARIZE (
fSales,
dProducts[NAME_PRODUCT],
"Prod Amount", [Sales Amount],
"Total amount", CALCULATE ( [Sales Amount], ALLSELECTED ( dProducts[NAME_PRODUCT] ) )
)
VAR cumulative_prod_amount =
ADDCOLUMNS (
sales_by_prod,
"Cumulative Amount",
VAR PrdAmt = [Prod Amount]
VAR Cumulate_amt =
FILTER ( sales_by_prod, [Prod Amount] >= PrdAmt )
RETURN
SUMX ( Cumulate_amt, [Prod Amount] )
)
VAR _pareto =
ADDCOLUMNS (
cumulative_prod_amount,
"paretopct", DIVIDE ( [Cumulative Amount], [Total amount] )
)
VAR ABC =
ADDCOLUMNS (
_pareto,
"Pareto Classification",
SWITCH ( TRUE (), [paretopct] <= 0.8, "A", [paretopct] <= 0.95, "B", "C" )
)
VAR Result =
SELECTCOLUMNS (
ABC,
"Product Name", dProducts[NAME_PRODUCT],
"Sales Amount", [Sales Amount],
"Pareto Amount", [paretopct],
"Pareto %", FORMAT ( [paretopct], "#0.00%" ),
"Pareto Classification", [Pareto Classification]
)
RETURN
Result
This will give you a new table for analysis.
If this works for you, please accept it as the solution.
@Rai_BI - That is a shame, I have also tried a Visual Calculation using your sample file and the DAX below:
Pareto =
VAR _cumulative = SUMX( WINDOW( 1, ABS, 0, REL, ROWS, ORDERBY( [Sales Amount], DESC )), [Sales Amount])
VAR _total = COLLAPSEALL( [Sales Amount], ROWS )
VAR _pareto = DIVIDE( _cumulative, _total )
RETURN
_pareto
// FORMAT( _pareto, "#0,0.0%")
However again this exceeds the resources when unfiltered. You will need to filter your visuals so this does not use all +50,000 of your products.
The Visual Calculation will have the fastest response, but you will not be able to format the result as a % (yet, still in preview) without using FORMAT. However, when you do this it will turn the result into a string and therefore cannot be ordered.
@Rai_BI - This is the measure I always use to create a pareto %
Total Qty Pareto by Product Type =
VAR total_qty =
CALCULATE ( [Total Qty], ALLSELECTED ( 'Product'[Product type] ) )
VAR pareto =
SUMX (
WINDOW (
0,
ABS,
0,
REL,
ALLSELECTED ( 'Product'[Product type] ),
ORDERBY ( [Total Qty], DESC )
),
[Total Qty]
)
RETURN
DIVIDE ( pareto, total_qty, 0 )
It is usually fairly optimal. If this does not work for you, perhaps you could try a Visual Calculation, as this will only calculate for the data within your visual. It is however in preview, so there are some limitations to them.
If my measure works, I'd be grateful if you could accept it as the solution.
Hi! @mark_endicott, thank you,
I wrote as you said, but take a look at the print below, my attempts exceeds available memory.
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Slow-problems-when-calculating-Paret...
这个回答非常棒,他的代码3和最后一个代码4,是有不同的。类似与v-tangjie-msft 反馈的。
Hi @Rai_BI ,
Through my testing, using the DIVIDE function optimizes the performance of the measure a little bit. However, due to the sheer volume of your data, I recommend filtering areas of your data and creating multiple visual objects.
Paretto% =
VAR vSales = [Sales Amount]
VAR vTempTable =
ADDCOLUMNS ( ALLSELECTED( 'dProducts' ), "Sales", [Sales Amount] )
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
DIVIDE(SUMX ( FILTER ( vTempTable, [Sales] >= vSales ), [Sales] ) ,vSalesTotal,0)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-tangjie-msft , than you for your help.
When a filter a some products, in fact, the visual loads quickly, but how can I use this measure in a slicer for example? I was trying to create a calculated column so as i could use it in a slicer. I don´t use the filters panel.
In slicer i need be able to filter Productos "A" or "B" or "C"
@Rai_BI - Now that I know you need a static analysis, your best solutuion will be to create a calculated table that does all the calculations in one.
Below is the DAX to create this table, you can change the ABC variable to suit your classifications & remove any unecessary columns from SELECTCOLUMNS() to remove them from the table.
To input this DAX go to Modeling > New Table.
VAR sales_by_prod =
SUMMARIZE (
fSales,
dProducts[NAME_PRODUCT],
"Prod Amount", [Sales Amount],
"Total amount", CALCULATE ( [Sales Amount], ALLSELECTED ( dProducts[NAME_PRODUCT] ) )
)
VAR cumulative_prod_amount =
ADDCOLUMNS (
sales_by_prod,
"Cumulative Amount",
VAR PrdAmt = [Prod Amount]
VAR Cumulate_amt =
FILTER ( sales_by_prod, [Prod Amount] >= PrdAmt )
RETURN
SUMX ( Cumulate_amt, [Prod Amount] )
)
VAR _pareto =
ADDCOLUMNS (
cumulative_prod_amount,
"paretopct", DIVIDE ( [Cumulative Amount], [Total amount] )
)
VAR ABC =
ADDCOLUMNS (
_pareto,
"Pareto Classification",
SWITCH ( TRUE (), [paretopct] <= 0.8, "A", [paretopct] <= 0.95, "B", "C" )
)
VAR Result =
SELECTCOLUMNS (
ABC,
"Product Name", dProducts[NAME_PRODUCT],
"Sales Amount", [Sales Amount],
"Pareto Amount", [paretopct],
"Pareto %", FORMAT ( [paretopct], "#0.00%" ),
"Pareto Classification", [Pareto Classification]
)
RETURN
Result
This will give you a new table for analysis.
If this works for you, please accept it as the solution.
Hi @Rai_BI ,
Measures cannot be placed on a slicer; you can create a calculated column as a slicer.
Paretto% =
VAR vSales = [Sales Amount]
VAR vTempTable =
ADDCOLUMNS ( ALL( 'dProducts' ), "Sales", [Sales Amount] )
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
DIVIDE(SUMX ( FILTER ( vTempTable, [Sales] >= vSales ), [Sales] ) ,vSalesTotal,0)
Pareto Classification =
VAR CumulativePercent = [Paretto%]
RETURN
SWITCH(TRUE(),
CumulativePercent <= 0.8, "A",
CumulativePercent <= 0.95, "B",
"C"
)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
11 | |
10 |