topic Re: Using a different weight in the last level of Sankey Chart in DAX Commands and Tips

Using a different weight in the last level of Sankey Chart

jeff569 — Wed, 25 Sep 2024 05:19:46 GMT

Hi Guys
I am creating a sankey chart that analyzes the electricity consumption. There are a total of 6 levels in the chart, and the weights used is the sum of consumption data. In the last level I want the data from the previous level to be multiplied by a value that can be changed dynamically by a parameter. This multiplied value is to be used as the weight that connects the 5th level and the 6th level. while I am trying to do this using the below attached code PowerBI automatically picks up the default value assigned and returns the weight in the last level and dynamic setting of the parameter doesnt work. The entire setup works seperately when I use a card to see the working. Can anyone please help me with this issue please.

I am using 1 parameter and 3 measures here:

1. StandardConsumption = SUM('Final (Trial)'[Consumption])

2. SelectedMultiplier =

SELECTEDVALUE('Parameter'[Parameter], 1)

3. MultipliedConsumption =

SUMX(

'Final (Trial)',

'Final (Trial)'[Consumption] * [SelectedMultiplier]

)

This is the Dax I am using to create my sankey chart:
SankeyData =
UNION(
SELECTCOLUMNS(
'Final (Trial)',
"Source", 'Final (Trial)'[Year],
"Destination", 'Final (Trial)'[Month],
"Year", 'Final (Trial)'[Year],
"Month", 'Final (Trial)'[Month],
"Consumption", [StandardConsumption] // Standard Consumption
),
SELECTCOLUMNS(
'Final (Trial)',
"Source", 'Final (Trial)'[Month],
"Destination", 'Final (Trial)'[Meter],
"Year", 'Final (Trial)'[Year],
"Month", 'Final (Trial)'[Month],
"Consumption", [StandardConsumption]
),
SELECTCOLUMNS(
'Final (Trial)',
"Source", 'Final (Trial)'[Meter],
"Destination", 'Final (Trial)'[Sub_Station],
"Year", 'Final (Trial)'[Year],
"Month", 'Final (Trial)'[Month],
"Consumption", [StandardConsumption] // Standard Consumption
),
SELECTCOLUMNS(
'Final (Trial)',
"Source", 'Final (Trial)'[Sub_Station],
"Destination", 'Final (Trial)'[Division],
"Year", 'Final (Trial)'[Year],
"Month", 'Final (Trial)'[Month],
"Consumption", [StandardConsumption]
),
SELECTCOLUMNS(
'Final (Trial)',
"Source", 'Final (Trial)'[Division],
"Destination", LOOKUPVALUE('eMI'[Emission], 'eMI'[Division], 'Final (Trial)'[Division]),
"Year", 'Final (Trial)'[Year],
"Month", 'Final (Trial)'[Month],
"Consumption", [MultipliedConsumption]
)
)

Re: Using a different weight in the last level of Sankey Chart

lbendlin — Wed, 25 Sep 2024 14:14:22 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 or anything not related to the issue or question.

If you are unsure how to upload data please refer to 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: Using a different weight in the last level of Sankey Chart

jeff569 — Thu, 26 Sep 2024 10:06:26 GMT

This is a sample of the data strucutre. I want the sankey to go like: year->Month->Meter->consumption-> Sub_Station->Division->Emission. I want the weight between all the levels to be Consumption but at the last level Division->Emission to be consumption*parameter.
Data Table 1 (Final (Trial)):

Year	Consumption	Month	Sub_Station	EM Number	Division
2018	18960	January	SS3	EM-216-SMDB-01	SF6
2018	16217	February	SS3	EM-216-SMDB-01	SF6
2018	20029	March	SS3	EM-216-SMDB-01	SF6
2018	23520	April	SS3	EM-216-SMDB-01	SF6
2018	24564	May	SS3	EM-216-SMDB-01	SF6
2018	24289	June	SS3	EM-216-SMDB-01	SF6
2018	26426	July	SS3	EM-216-SMDB-01	SF6
2018	25667	August	SS3	EM-216-SMDB-01	SF6
2018	20659	September	SS3	EM-216-SMDB-01	SF6
2018	21308	October	SS3	EM-216-SMDB-01	SF6
2018	21392	November	SS3	EM-216-SMDB-01	SF6
2018	15469	December	SS3	EM-216-SMDB-01	SF6
2019	15265	January	SS3	EM-216-SMDB-01	SF6
2019	14951	February	SS3	EM-216-SMDB-01	SF6
2019	16666	March	SS3	EM-216-SMDB-01	SF6
2019	17710	April	SS3	EM-216-SMDB-01	SF6
2019	21727	May	SS3	EM-216-SMDB-01	SF6
2019	21881	June	SS3	EM-216-SMDB-01	SF6
2019	22369	July	SS3	EM-216-SMDB-01	SF6
2019	20399	August	SS3	EM-216-SMDB-01	SF6
2019	25053	September	SS3	EM-216-SMDB-01	SF6
2019	23051	October	SS3	EM-216-SMDB-01	SF6
2019	21593	November	SS3	EM-216-SMDB-01	SF6
2019	18952	December	SS3	EM-216-SMDB-01	SF6

Data Table 2(eMI):

Emission	Division
CO2 SF6	SF6
CO2 Aegis	AEGIS
CO2 OIL	OIL
CO2 FZE	FZE

Re: Using a different weight in the last level of Sankey Chart

lbendlin — Thu, 26 Sep 2024 15:37:12 GMT

I don't see a parameter column.

Instead of a Sankey maybe use a Decomposition tree?

Re: Using a different weight in the last level of Sankey Chart

jeff569 — Fri, 27 Sep 2024 05:13:25 GMT

Thank you for the response but my boss is keen on using Sankey is there way to achieve my expected reuslts using sankey itself.
my parameter table looks like :

Parameter
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
3
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
4
4.1
4.2
4.3
4.4
4.5
4.6
4.7
4.8
4.9
5
5.1
5.2
5.3
5.4
5.5
5.6
5.7
5.8
5.9

Re: Using a different weight in the last level of Sankey Chart

lbendlin — Sun, 29 Sep 2024 22:36:27 GMT

Your first sample table (Final (Trial)) has all the same values in the last three columns. I don't see how you can get any chart out of that, let alone a Sankey chart. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.