Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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)
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
@Anonymous What do you mean by creating new type in New table? what is that?
Can you please show and tell?
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
71 | |
70 | |
43 | |
31 | |
26 |
User | Count |
---|---|
89 | |
49 | |
44 | |
38 | |
37 |