topic Re: Dynamic Grouping and converting LOD calculations to power bi dax in DAX Commands and Tips

Dynamic Grouping and converting LOD calculations to power bi dax

Anonymous — Wed, 23 Apr 2025 10:42:22 GMT

Hello All,

I am facing an issue while migrating the Tableau dashboard to Power BI.

There is one dynamic bucket in Tableau, and based on that Qoq weighted discount, YoY weighted discount metrics are calculated. I am using direct query and my fiscalqtr column is in text format like 2023q1, 2023q2, 2025q1, 2025q4, etc

The loading time for the matrix with my calculations, which I have done in Power BI, shown below, is almost 70 seconds, even though I have aggregated the data at the source.

Could you please check and tell me if there is any other solution?

also in tableau we have some context filter on product_revenue_class_category_new,active_status_qtr_new,booking_type and dimension filter on multiarea,geo,coverage_segmentation.

I am getting correct numbers for all context filters as slicers on Power BI, but for the dimension filter numbers are not correct. If I make the context in Tableau, then I get the correct result, but I cannot do so.

Tableau calculations:

Dynamic bucket

Booking Range

IF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} < 0 then "<0K"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} >= 0 AND {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} <= 50000 then "0K-50K"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} > 50000 AND {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} <=100000 then "50K-100K"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} > 100000 AND {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} <=250000 then "100K-250K"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} > 250000 AND {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} <=500000 then "250K-500K"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} > 500000 AND {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])}<=1000000 then "500K-1M"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} > 1000000 AND {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} <=2500000 then "1M-2.5M"

ELSEIF {fixed [Sales Order Number],[Opportunity Number],[Fiscal Qtr]:sum([Usd Booking Amount ])} > 2500000 THEN ">2.5M" END

calculation for weighted discount

Calculation1 (copy 2)=

SUM({Fixed [Fiscal Qtr],[Sales Geography ],[Booking Range ]:SUM([Usd Extended Discount Amount ])})/sum({FIXED [Fiscal Qtr]:SUM([Usd Extended Discount Amount ])})

discount weighted=

[Calculation1 (copy 2)]*ATTR({ FIXED [Fiscal Qtr]:([Discount %])})

What I have done I create a separate disconnect segment table and then calculate the discount

Calculation1 (copy 2) =

VAR DiscountAmount_Grouped =

CALCULATE(

[USD Extended Discount Amount]

)

VAR DiscountAmount_Quarterly =

CALCULATE(

[USD Extended Discount Amount],

ALLEXCEPT(DNM_DRILL_THROUGH,

DNM_DRILL_THROUGH[FISCAL_QTR]

,DNM_DRILL_THROUGH[PRODUCT_REVENUE_CLASS_CATEGORY_NEW]

,DNM_DRILL_THROUGH[BOOKING_TYPE],DNM_DRILL_THROUGH[ACTIVE_STATUS_QTR_NEW]

)

RETURN

DIVIDE(DiscountAmount_Grouped, DiscountAmount_Quarterly, 0)

discount weighted=

VAR DiscountPercentage_Quarterly = CALCULATE( [Discount %], ALLEXCEPT(DNM_DRILL_THROUGH, DNM_DRILL_THROUGH[FISCAL_QTR] ,DNM_DRILL_THROUGH[PRODUCT_REVENUE_CLASS_CATEGORY_NEW] ,DNM_DRILL_THROUGH[BOOKING_TYPE],DNM_DRILL_THROUGH[ACTIVE_STATUS_QTR_NEW] ) )

RETURN [Calculation1bookingrange] * DiscountPercentage_Quarterly

Discount_weighted_BR =

calculate([Discount % Weighted booking range],

FILTER(SUMMARIZE(DNM_DRILL_THROUGH,DNM_DRILL_THROUGH[FISCAL_QTR],DNM_DRILL_THROUGH[OPPORTUNITY_NUMBER],DNM_DRILL_THROUGH[SALES_ORDER_NUMBER]),

[Usd Extended Discount Amount ]>=MIN('Segment Table'[MIN])&&[Usd Extended Discount Amount ]<MAX('Segment Table'[MAX])))

Re: Dynamic Grouping and converting LOD calculations to power bi dax

lbendlin — Wed, 23 Apr 2025 13:48:44 GMT

Power BI does not support dynamic binning the way Tableau does. You need to bring your own, predefined buckets.

Re: Dynamic Grouping and converting LOD calculations to power bi dax

Anonymous — Thu, 24 Apr 2025 09:43:09 GMT

amitchandak Hello Amit, I am a regular YouTube follower of your channel. Could you help me resolve this issue or guide me on how to resolve it?

Re: Dynamic Grouping and converting LOD calculations to power bi dax

Anonymous — Tue, 29 Apr 2025 06:56:39 GMT

Hi Anonymous,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

Re: Dynamic Grouping and converting LOD calculations to power bi dax

Anonymous — Fri, 02 May 2025 06:01:15 GMT

Hi Anonymous,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

Re: Dynamic Grouping and converting LOD calculations to power bi dax

Anonymous — Mon, 05 May 2025 05:18:15 GMT

Hi Anonymous,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

Re: Dynamic Grouping and converting LOD calculations to power bi dax

Anonymous — Tue, 06 May 2025 08:32:13 GMT

I have developed my solution using disconnected segment table, dax using summarize