cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Calculate cost based on category from different table

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

3 REPLIES 3
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

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

Helper I

Can you please show and tell?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.