Forum Discussion
Add custom field to selectedvalue column in matrix
Anonymous
But currType never be equal to "Costs", as 'New Table' does not have "Costs" type, and always will work else statment in your exapmle. "Costs" field exists only in Table1[Type]
https://filebin.net/fxc8y76kim6yisxk
HI Analitika,
If you want to show the expand fields that not exist in raw table, you need to use unconnected table fields as category or they will be filtered by power bi before you do custom with them.
Sample formula:
Measure =
VAR suma =
SUM ( Table1[sum] )
VAR savi =
SUMX (
'Types(Unconnected)',
CALCULATE (
CALCULATE (
CALCULATE ( SUM ( Table1[sum] ), VALUES ( Table1[Route_ID] ) )
+ MAX ( Table1[Fuel Consumped] ),
Table1[type code] <> 2
)
+ SUM ( Table1[Fuel filled] ),
FILTER ( Table1, [type code] IN VALUES ( 'Types(Unconnected)'[id] ) ),
VALUES ( 'Date'[sort_month] )
)
)
VAR bp = suma - savi
VAR marza =
DIVIDE ( bp, suma )
RETURN
IF (
ISINSCOPE ( Types2[Name] ),
SWITCH (
SELECTEDVALUE ( Types2[Name] ),
"Sales", IF ( ISINSCOPE ( 'Types(Unconnected)'[Name] ), BLANK (), suma ),
"Costs",
IF (
SELECTEDVALUE ( 'Types(Unconnected)'[Name] )
IN { "Fuel, consumed", "Ad Blue, consumed" },
SWITCH (
SELECTEDVALUE ( 'Types(Unconnected)'[Name] ),
"Fuel, consumed", IF ( MAX ( 'Date'[Date] ) IN VALUES ( Table1[Date] ), -1 ),
"Ad Blue, consumed", IF ( MAX ( 'Date'[Date] ) IN VALUES ( Table1[Date] ), -2 ),
BLANK ()
),
savi
),
"Profit", IF ( ISINSCOPE ( 'Types(Unconnected)'[Name] ), BLANK (), bp )
)
)
Notice:
1. Since relationship has been breaks, it mean you need to manually apply filter on row table fields based on current fields values.
2. -1,-2 are placeholders of added field values, I already add if statement and conditions to remove not match fields display on the visual.
3. I modify the calculate formula but 'savi' part seems not calculate correctly, you can try to fix them. (logic: calculate without raw types table and use 'in' operator to filter calculate result with new table field values)
Regards,
Xiaoxin Sheng
Anonymous
3. I modify the calculate formula but 'savi' part seems not calculate correctly, you can try to fix them. (logic: calculate without raw types table and use 'in' operator to filter calculate result with new table field values)
that because i ask for help here, in that way it totaly wrong counting on totals and not include placeholders values, also in example only 2 Types(Unconnected), but imagine what i have them over 100
Hi Analitika,
>>that because i ask for help here, in that way it totaly wrong counting on totals and not include placeholders values,
They may related to multiple aggregates on the calculation, take a look at the following blog if helps.
Measure Totals, The Final Word
>> also in example only 2 Types(Unconnected), but imagine what i have them over 100
Ok, I think my formulas may not suitable to handle this scenario.
How about direct append some template blank row to the raw table with corresponding type codes? After these steps, these types exist in your table and you can simply use DAX formulas to replace display contents.
Regards,
Xiaoxin Sheng