Assigning a new conditional column to an aggregated table via DAX

[dogfish]

I'm trying to build a dynamic table for a clustred bar chart visual where I have a source table that's being filtered via a multi-select slicer.

What i want to do is to summarize the sliced data and aggregate a quantity column containing positive and negative values. Depending on whether the total quantity value is positive or negative, I assign a label, say label P for the positive total, and N for negative totals on the aggregated data. I then want to put this new grouped table with the total positive/negative quantity and the labels for those total quantities in the clustered bar chart where the P and N label would be used for the legend so the end result would be I would see the negative values after aggregation on the left of 0 and the total positive values on the right of zero. Then depending on the slicer values, new data gets aggregated, the totals change and i would see new positive and negative aggregated values.

I tried using summarizecolumns first then pass the resulting table from that to an addcolumns function where i was hoping to check the aggregated data then add the conditional column. What's happening is that it's doing the column adding not on the aggregated result but the original data from the source table that's been sliced so i'm getting the totals of the negative numbers on the left of zero and the total of all the positive numbers on the right of zero instead of doing the aggregate first then assigning the positive or negative label. I was thinking maybe there's a way to manually do the filtering via DAX so based on the slicer selection, manually filter the source table via DAX instead of using relationships but i couldn't do it as concatenatex doesn't seem to be built for this purpose.

Any help would be appreciated

thanks

[v-veshwara-msft]

Hi @dogfish ,
As we haven't heard from you in a while, just following up on your query. If you still need assistance, please let us know. Could you also share a sample PBIX file (without any sensitive information) or some sample data to assist better.
Thank you.

[v-veshwara-msft]

Hi @dogfish ,
Just checking in to see if you have found a solution for your query. If further assistance is needed, please reach out.

If possible, could you also share a sample PBIX file (without any sensitive information) or some sample data that mimics your scenario? This will help us better understand your requirement and provide possible approaches.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[v-veshwara-msft]

Hi @dogfish ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out. Could you also share a sample PBIX file (without any sensitive information) or some sample data to assist better.
Thank you.

[pankajnamekar25]

Hello @dogfish 

try this 

Create the Conditional Label Column

Once we have the aggregated data, we can then add the conditional column that checks if the sum is positive or negative, and assigns the corresponding label ("P" or "N").

AggregatedTableWithLabel =
VAR AggregatedTable =
SUMMARIZECOLUMNS(
'YourTable'[GroupColumn1], -- Replace with actual columns you want to group by
'YourTable'[GroupColumn2],
"TotalQuantity", SUM('YourTable'[Quantity]) -- Aggregated value for quantity
)
RETURN
ADDCOLUMNS(
AggregatedTable,
"Label",
IF([TotalQuantity] > 0, "P", "N") -- Conditional label based on TotalQuantity
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[dogfish]

hi. no i already did this and this doesn't do what i need for my requirement. I'm doing a different work around as my requirements need a different approach.

[v-veshwara-msft · Accepted solution]

Hi @dogfish ,

Thanks for your update and for sharing that you’ve tried the suggested approach. Noted that it doesn’t meet your specific requirements and that you’re pursuing a different workaround. Please feel free to share any findings or questions along the way if you need further guidance.

If possible, could you also share a sample PBIX file (without any sensitive information) or some sample data that mimics your scenario? This will help us better understand your requirement and provide possible approaches.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.