topic Re: Table icons should change when combined the 2 same account rows based on the high risks in DAX Commands and Tips

Table icons should change when combined the 2 same account rows based on the high risks

Anonymous — Mon, 28 Oct 2024 16:00:47 GMT

Hi Experts,

I am working on the scenario to combine 2 rows (same account names) with all the other columns based on the logics . Below the sample table which has account details .

2 and 3 rows have same account name , when this kind of scenario occurs , we have to summing up forecast and revenue values and applied some logics behind the CM% , all works well .
But the requirement we are working for Scope and Schedule columns , for example in the above table Scope is "Amber & Green" for same account names (2&3 rows) , in that case the color icon should be changed into "Amber" (comparatively Amber is higher risk than Green) , if we have "Amber & Red" color icons for any 2 rows having same accounts then the expected result should be "Red" (comparatively Red is higher risk than Amber).
We should combine the 2 same account rows by Single row with applying the above requirement .

So single row for account will come only when i removed scope and schedule columns from the table as below but that is not expected :

Without combining i cannot able to showcase a single row for an account.

Please help to sort this for me ..
let me know if anything more info required..

Thanks

Re: Table icons should change when combined the 2 same account rows based on the high risks

lbendlin — Mon, 28 Oct 2024 22:31:26 GMT

Re: Table icons should change when combined the 2 same account rows based on the high risks

Anonymous — Tue, 29 Oct 2024 13:34:36 GMT

lbendlin Thanks a lot for your support , it works well when there is no selection (ALL) on my slicer.

But when i filter some option is slicer then still the combined row only visible.

We have to show single row (from combined row) based on the slicer selection.

When slicer offer type is ALL then we can show combined row , but when we select some selection from the slicer then it should show only specific row only .
Below the sample slicer and table

For example , the above table has offer type and scope logic added for reference , once we removed those 2 columns then automatically the combined rows will show.
In the above slicer we have offer type (eg : RPA , GEN AI/AI) , when i select RPA , values are changing as per the selection but the combined column is not changing and this should be changed as per offer type selection (expected result is "Green")

Is there any option to achieve this ?

Please help ..

Re: Table icons should change when combined the 2 same account rows based on the high risks

lbendlin — Tue, 29 Oct 2024 16:08:33 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Table icons should change when combined the 2 same account rows based on the high risks

Anonymous — Tue, 05 Nov 2024 05:41:44 GMT

(ACCOUNT)	(OFFER TYPE)	(SCOPE)	(SCOPE COMBINED)	(FORECAST)	(REVENUE)	(CM%)
AAA	X	RED	RED	445354	454367	35%
AAA	Y	GREEN	RED	8676	9764	35%
BBB	Y	AMBER	AMBER	123445	976765	35%
BBB	X	GREEN	AMBER	33555	87654	35%

lbendlin

above the sample data , in that SCOPE and SCOPE COMBINED columns are calculated columns which we have developed already to display the risks in color icons .
We have a OFFER TYPE slicer in the page and In the slicer we have offer types (eg : X , Y , etc) When slicer OFFER TYPE is "ALL" then we can show combined row as below :

Acc	Scope Comb.	forecast	revenue	CM%
AAA	RED	454030 (sum of both AAA account forecast values)	464131	35%
BBB	AMBER	.....	.....	35%

but when we select some selection from the slicer (X or Y) then it should show only specific respective row data .
Example : when i select "X" in the slicer , the table should show only X related data in the table expected as below : (removed OFFER TYPE & SCOPE columns)

ACCOUNT	SCOPE COMBINED	FORECAST	REVENUE	CM%
AAA	RED	445354	454367	35%
BBB	GREEN	33555	87654	35%

Scope combined DAX we created :

Scope Combined =

VAR A = 'Test Delivery Updates'[Account Name]

VAR B = ADDCOLUMNS(SUMMARIZE(FILTER('Test Delivery Updates','Test Delivery Updates'[Account Name]=A),'Test Delivery Updates'[Scope logic]),"Scope",SWITCH('Test Delivery Updates'[Scope logic],"Red",3,"Amber",2,"Green",1,0))

RETURN MAXX(TOPN(1,B,[Scope]),'Test Delivery Updates'[Scope logic])

So far the combined rows are working fine when OFFER TYPE is "ALL" , so we want to fix the single offer type selection from the slicer.

Hope it is clear .. let me know if you require more data.

Thanks