IF function with variants

[t-atgeor]

Hi,

am trying to establish a Summary table including sales and costs, that would have fields such as Curr Month, Prev Month, Curr Quarter, Prev Quarter as in example below. At the same time, each field is a calculated measure from other tables ('Sales', 'Costs'). Nevertheless, numbers should be flexible as to read all slicers/ fliters (for example Filter by month)

Once I establish a very simple formula such as: If('Summary'[Metric]="Sales",TOTALMTD(SUM('Sales'[Sales USD]),'Sales'[Fiscal Month]),If('Summary'[Metric]="Costs",TOTALMTD(SUM('Costs'[Costs USD]),'Costs'[Fiscal Month),"N/A"))  , I receive the following error: Expressions that yield variant data-type cannot be used to define calculated columns.

The filter is a relationship in both directions on Fiscal Month level.

I apologize, am a beginner in Dax and PowerBI, and am not familiar with the correct ways to establish the Table Summary.

Thank you for the time and regards,

Atanas

[v-shex-msft · Accepted solution]

Hi @t-atgeor,

Based on my understanding, you want to get the summarize table of cost and sales which you choose, right?

If it is a case, you can refer to below steps: (use union function and measure to create a dynamic table visual)

Data tables:
Cost.                                                     
 

Sales.

 

DateTable = VALUES(Sales[Fiscal Month])

 

Add a measure to get selected date:

Selected Date = IF(HASONEVALUE(DateTable[Fiscal Month]),VALUES(DateTable[Fiscal Month]),BLANK())

Use union function to create a table with two records:

Table = UNION(ROW("Metric","Sales"),ROW("Metric","Costs"))

 

Add measures to calculate current value, previous value, current quarter, previous quarter, total.

The type of record:

Curr Type = LASTNONBLANK('Table'[Metric],'Table'[Metric])

Current month value:

Curr value = if([Curr Type]="Costs",
LOOKUPVALUE(Cost[Costs USD],Cost[Fiscal Month],[Selected Date]),
LOOKUPVALUE(Sales[Sales USD],Sales[Fiscal Month],[Selected Date]))

Previous month value:

Prev value = 
var currtype= LASTNONBLANK('Table'[Metric],'Table'[Metric])
var temp=DATE(YEAR([Selected Date]),MONTH([Selected Date])-1,DAY([Selected Date]))
return
if([Curr Type]="Costs",
if(LOOKUPVALUE(Cost[Costs USD],Cost[Fiscal Month],temp)<> BLANK(),
LOOKUPVALUE(Cost[Costs USD],Cost[Fiscal Month],temp),0),
if(LOOKUPVALUE(Sales[Sales USD],Sales[Fiscal Month],temp)<>BLANK(),
LOOKUPVALUE(Sales[Sales USD],Sales[Fiscal Month],temp),0 ))

Current quarter value:

Curr Quarter = 
var quarter = SWITCH(MONTH([Selected Date]),1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)
var minMonth =(quarter-1) *3 +1
var maxMonth=quarter* 3
return
if([Curr Type]="Costs",
SUMX(FILTER(ALL(Cost),
AND(MONTH(Cost[Fiscal Month])>=minMonth,MONTH(Cost[Fiscal Month])<=maxMonth)&&YEAR(Cost[Fiscal Month])=YEAR([Selected Date])),
Cost[Costs USD]),
SUMX(FILTER(ALL(Sales),
AND(MONTH(Sales[Fiscal Month])>=minMonth,MONTH(Sales[Fiscal Month])<=maxMonth)&&YEAR(Sales[Fiscal Month])=YEAR([Selected Date])),
Sales[Sales USD]))

previous quarter value:

Prev Quarter = 
var quarter = SWITCH(MONTH([Selected Date]),1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)
var minMonth =(quarter-2)*3 +1
var maxMonth=(quarter-1)*3
var currYear= YEAR([Selected Date])
return
if(quarter<>1,
if([Curr Type]="Costs",
SUMX(FILTER(ALL(Cost),
AND(MONTH(Cost[Fiscal Month])>=minMonth,MONTH(Cost[Fiscal Month])<=maxMonth)&&YEAR(Cost[Fiscal Month])=YEAR([Selected Date])),
Cost[Costs USD]),
SUMX(FILTER(ALL(Sales),
AND(MONTH(Sales[Fiscal Month])>=minMonth,MONTH(Sales[Fiscal Month])<=maxMonth)&&YEAR(Sales[Fiscal Month])=YEAR([Selected Date])),
Sales[Sales USD])),
if([Curr Type]="Costs",
SUMX(FILTER(ALL(Cost),
AND(Cost[Fiscal Month]>=DATE(currYear-1,10,1),Cost[Fiscal Month]<=DATE(currYear-1,12,31))),
Cost[Costs USD]),
SUMX(FILTER(ALL(Sales),
AND(Sales[Fiscal Month]>=DATE(currYear-1,10,1),Sales[Fiscal Month]<=DATE(currYear-1,12,31))),
Sales[Sales USD])))

Total = [Curr Quarter]+[Prev Quarter]

Create visuals to display the result.
Slicer:

 

Table visual:

 

Notice: if you didn’t select a date or select multiple dates, the table visual will get an error.

Regards,
Xiaoxin Sheng