Calculate cost based on category from different table

[zzzsharepoint]

We have 3 tables in power Bi. First table has column Cluster, Category and Cost.

Cluster	Category	Cost
A	CPU	10
A	Network	111
A	Disks	21
B	CPU	91

Second table has column Cluster, Projects in each cluster, timeseries ratio , non-timeseries ratio

Cluster	Projects	Blow ratio	Non-Blow ratio
A	Delta	0.423	0.577
A	Beta	0.523	0.477
A	XYX	0.19	0.81
B	YUSE	0.322	0.678
B	BYLO	0.2122	0.7878

and third table has column as Cluster, Kind which has value as Blow and non-Blow , Category and the ratio of each category.

Cluster	Kind	Category	Value
A	Blow	CPU	0.777
A	Blow	Network	0.542
A	Non-Blow	CPU	0.211
B	Blow	CPU	0.982
A	Blow	Disks	0.18
A	Non- Blow	Disks	0.122
A	Non- Blow	Network	0.776

and so On....

How can I calculate the cost of each category with Blow and Non-Blow Kind for each of the project in each cluster.

So at the end I will like to have two tables visuals one each for Blow and other for Non-Blow: This is for Blow kind(Category cost for each cluster * Blow ratio for that project in that cluster* Value of that category of that kind for that cluster from 3rd table)

Cluster	Project	Category	Cost
A	Delta	CPU	3.25
A	Delta	Network	25.25
A	Delta	Disks	1.59
A	Beta	CPU	4.04

[v-shex-msft]

Hi @zzzsharepointk,

You can create a new table with time series and non-time series two types. Then you can extract the current type and add switch in your formula to calculated with different fields based on the type values.(you can add new table field to table visual 'visual level filter' to filter records)

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

formual =
VAR currType =
    SELECTEDVALUE ( NewTable[Type] )
RETURN
    CALCULATE (
        SWITCH (
            currType,
            "timeseries", MAX ( Table2[Blow ratio] ),
            "non-timeseries", MAX ( Table2[Non-Blow ratio] )
        ),
        ALLSELECTED ( Table2 ),
        VALUES ( Table2[Cluster] ),
        VALUES ( Table2[Projects] )
    )
        * CALCULATE (
            SUM ( Table1[Cost] ),
            ALLSELECTED ( Table1 ),
            VALUES ( Table2[Cluster] ),
            VALUES ( Table1[Category] )
        )

Regards,

Xiaoxin Sheng

[zzzsharepoint]

@v-shex-msft  What do you mean by creating new type in New table? what is that?

[zzzsharepoint]

Can you please show and tell?