Grouped summaries with conditional filtering

[pbiforum_123]

We would like to achieve the below using the import mode in Power BI. We have already achieved the one in BOLD using visual filter. I need your help to achieve the outer query. Please kindly help 

SELECT
HsCode,
SUM(SupplementaryUnitQty) AS TotalSupplementaryUnitQty
FROM
[dbo].[CBAM_Goods_InScope]
WHERE
[EntryIdentifier] IN (
SELECT [EntryIdentifier]
FROM [dbo].[CBAM_Goods_InScope]
GROUP BY [EntryIdentifier]
HAVING SUM([CustomsValue]) > [Parameter]
)

Visual Filter which is currently used:

ShowRow =

VAR Threshold = [SelectedThreshold]

VAR TotalForEntry =

    CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]), ALLEXCEPT('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]))

RETURN

    IF(TotalForEntry > Threshold, 1, 0)

[grazitti_sapna]

Hi @danextian,

Try using below DAX for outer query:-

Total SupplementaryUnitQty (Filtered) =
VAR Threshold = [SelectedThreshold]
RETURN
CALCULATE(
SUM('CBAM_Goods_InScope'[SupplementaryUnitQty]),
FILTER(
'CBAM_Goods_InScope',
CALCULATE(
SUM('CBAM_Goods_InScope'[CustomsValue]),
ALLEXCEPT('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier])
) > Threshold
)
)

Use it in table matrix as 

In a table or matrix visual:

• Put 'CBAM_Goods_InScope'[HsCode] in rows

• Add this new measure Total SupplementaryUnitQty (Filtered) in values

This will return the correct sum per HsCode based on your SQL condition.

You can also apply filtering based on the show row measure, Use below DAX

ShowRow =
VAR Threshold = [SelectedThreshold]
VAR TotalForEntry =
CALCULATE(
SUM('CBAM_Goods_InScope'[CustomsValue]),
ALLEXCEPT('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier])
)
RETURN
IF(TotalForEntry > Threshold, 1, 0)

Then use ShowRow = 1 as a page-level filter if needed.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[pbiforum_123]

I dont need result in Matrix I need it as a table or virtual table which can be used for further part of calculation...

[grazitti_sapna]

Hi @pbiforum_123,

Okay got it, try below solutions

Below virtual table filters rows dynamically and can be used within a measure

VAR Threshold = [SelectedThreshold]
VAR FilteredTable =
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]),
"TotalValue", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalValue] > Threshold
)
VAR ResultTable =
CALCULATETABLE(
SUMMARIZE(
'CBAM_Goods_InScope',
'CBAM_Goods_InScope'[HsCode],
"TotalSupplementaryUnitQty", SUM('CBAM_Goods_InScope'[SupplementaryUnitQty])
),
TREATAS(
SELECTCOLUMNS(FilteredTable, "EntryIdentifier", 'CBAM_Goods_InScope'[EntryIdentifier]),
'CBAM_Goods_InScope'[EntryIdentifier]
)
)
RETURN
ResultTable

If You Want a Physical Calculated Table, use below DAX

Filtered_HSCode_SuppQty =
VAR Threshold = [SelectedThreshold]
VAR ValidEntryIds =
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]),
"TotalCustomsValue", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalCustomsValue] > Threshold
)
RETURN
SUMMARIZE(
FILTER(
'CBAM_Goods_InScope',
'CBAM_Goods_InScope'[EntryIdentifier] IN
SELECTCOLUMNS(ValidEntryIds, "EntryIdentifier", 'CBAM_Goods_InScope'[EntryIdentifier])
),
'CBAM_Goods_InScope'[HsCode],
"TotalSupplementaryUnitQty", SUM('CBAM_Goods_InScope'[SupplementaryUnitQty])
)

If you want to use it to get the total sum across filtered EntryIdentifiers

TotalSupplementaryUnitQty_Measure :=
VAR Threshold = [SelectedThreshold]
VAR ValidEntries =
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]),
"TotalVal", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalVal] > Threshold
)
VAR Result =
CALCULATE(
SUM('CBAM_Goods_InScope'[SupplementaryUnitQty]),
TREATAS(
SELECTCOLUMNS(ValidEntries, "EntryIdentifier", 'CBAM_Goods_InScope'[EntryIdentifier]),
'CBAM_Goods_InScope'[EntryIdentifier]
)
)
RETURN
Result

[pbiforum_123]

@grazitti_sapna Thanks and I tried this... Summarize will not work based on the dynamic parameter as I mentioned in my post earlier... It is always considering 150 which is default value not the value which is getting passed...

[v-hjannapu]

Hi @pbiforum_123 ,

You're correct  when we use SUMMARIZE in a calculated table, it doesn’t pick up the dynamic slicer value like [SelectedThreshold]. It only takes the default value at the time of refresh. To make it work based on what the user selects in the slicer, it’s better to use a measure-based virtual table. That method will react properly to the slicer and can be used in further calculations also.

If this answer was helpful, please consider marking it as Accepted Solution and giving a Kudos, it helps the community.
Best Regards,
Harshitha.

[pbiforum_123]

@v-hjannapu Thanks for your response. Can you please let me know how to create the measure based virtual table to filter the data based on my requirement.

[mark_endicott]

@pbiforum_123 @v-hjannapu - I have already done this and attached the file for you to use. See below for the post it is further up the chain. 

@pbiforum_123 - if the file I have already supplied you has not resolved your issue, please can you explain why?

[v-hjannapu]

Hi  @pbiforum_123,
@mark_endicott ,thank you and really appreciate the detailed support and working file you have provided.

Yes, completely agree with your approach here. As you rightly mentioned, calculated tables won’t respond to slicer/parameter changes at runtime. The dynamic measure with virtual filtering using TREATAS and filtered EntryIdentifiers is the right and optimal solution in this case.

@pbiforum_123  Please do revisit Mark’s earlier post and test the attached PBIX. If you are still seeing differences between your SQL output and Power BI results, could you kindly share what exactly is not matching? That’ll help us assist more precisely.

Best regards,
Harshitha .

[v-hjannapu]

Hi @pbiforum_123,

Just wanted to check if you had the opportunity to review the suggestion provided  by @pbiforum_123  

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

Harshitha.

[v-hjannapu]

Hi @pbiforum_123 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[v-hjannapu]

Hi @pbiforum_123,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[pbiforum_123]

Thanks! Sorry for late response. I am back today as there was family emergency. Will check and update in couple of days.

[v-hjannapu]

Hi @pbiforum_123,

We really appreciate your efforts and for letting us know the update on the issue.

Please continue using fabric community forum for your further assistance.

Regards,
Harshitha.

[v-hjannapu]

Hi @pbiforum_123,
Could you please let us know the ETA by when you will be able to test the provided solution? This will help us to know when we can follow up. If you encounter any challenges during implementing the provided solution, please inform us and we’ll be glad to assist.

Regards,
Harshitha.

[pbiforum_123]

@v-hjannapu Pls close this thread. This is something I wont be working on it now. Thanks

[mark_endicott]

@v-hjannapu - I have supplied a working solution in DAX. Asking why numbers do not match to a SQL query is a dangerous question as there could be any number of reasons outside Power BI for this. 

Please can you accept my solution. 

[grazitti_sapna]

Hi @pbiforum_123,

Have you tried this solution?

[mark_endicott]

@pbiforum_123 - If you just need DAX for the outer query it's this:

SUMX(VALUES('CBAM_Goods_InScope'[HsCode]), 'CBAM_Goods_InScope'[SupplementaryUnitQty] )

The visual filter then provides the rest. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[pbiforum_123]

@mark_endicott  Thanks for your time but this is not working. I am getting this error.

[mark_endicott]

@pbiforum_123 - Sorry my bad, missed the aggreagtion. 

SUMX(VALUES('CBAM_Goods_InScope'[HsCode]), SUM( 'CBAM_Goods_InScope'[SupplementaryUnitQty] ) )

I was typing too quickly!

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!