Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zzzsharepoint
Helper I
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
v-shex-msft
Community Support
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.

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

 

Can you please show and tell?

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors