Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table
Date | NameCode | SubNameCode | Name | Name Price | Sub name | SubName price | Pcs | |
43490 | 10070 | 131 | Tea | Salary | 0,061705 | 0 | ||
43490 | 10070 | 6000 | Tea | Sticker1 | 0,001185 | 2010 | ||
43490 | 10070 | 421000 | Tea | Stick1 | 0,00194 | 2400 | ||
43490 | 10070 | 10070 | Tea | 0,31777 | 2000 | |||
43490 | 10070 | 20310001 | Tea | Leaf1 | 0,204945 | 102500 | ||
43490 | 10070 | 401000 | Tea | Stick2 | 0,004275 | 3520 | ||
43490 | 10070 | 310050 | Tea | Box1 | 0,04372 | 2024 | ||
43490 | 10070 | Tea | 0 | 2000 | ||||
43490 | 10070 | 6032 | Tea | 0 | 0 | |||
43514 | 10070 | 131 | Tea | Salary | 0,060883 | 0 | ||
43514 | 10070 | 6000 | Tea | Sticker1 | 0,001187 | 1024 | ||
43514 | 10070 | 20310001 | Tea | Leaf1 | 0,003788 | 1793 | ||
43514 | 10070 | 401000 | Tea | Stick2 | 0,201197 | 51250 | ||
43514 | 10070 | 10070 | Tea | 0,30843 | 1019 | |||
43514 | 10070 | 310050 | Tea | Box1 | 0,041374 | 976 | ||
43514 | 10070 | Tea | 0 | 1019 | ||||
43514 | 10070 | 6032 | Tea | 0 | 0 |
Need measure to get sum of each Subname by SubName price and Sum of each SubName Pcs
so must be something like that
AllSubNamePrice =
Calculate(
SUM([SubnamePrice]),
FIlter([NameCode]<>[SubNameCode]),
)
AllSubNameCount =
Calculate(
SUM([Pcs]),
FIlter([NameCode]<>[SubNameCode]),
)
1pcsPrice =
Divide(
AllSubNamePrice ,
AllSubNameCount ), // if "Salary", then get value from [Pcs] where [NameCode]=[SubNameCode]
)
But measure sum all alue but not by group and dates
Expected result is:
Sub name | Sum | Count | 1pcs |
Salary | 0,1226 € | - | 0,0614276 € |
Box1 | 0,0851 € | 3 000 | 0,0000284 € |
Leaf1 | 0,2087 € | 104 293 | 0,0000020 € |
Stick1 | 0,0019 € | 2 400 | 0,0000008 € |
Stick2 | 0,2055 € | 54 770 | 0,0000038 € |
Sticker1 | 0,0024 € | 3 034 | 0,0000008 € |
Solved! Go to Solution.
Hi, @Analitika
Based on your description, you may create three measures as below.
AllSubNamePrice =
CALCULATE(
SUM('Table'[SubName price]),
FILTER(
'Table',
'Table'[NameCode]<>'Table'[SubNameCode]
)
)
AllSubNameCount =
IF(
SELECTEDVALUE('Table'[Sub name])<>"Salary",
CALCULATE(
SUM('Table'[Pcs]),
FILTER(
'Table',
'Table'[Name Price]<>'Table'[SubNameCode]
)
),
CALCULATE(
SUM('Table'[Pcs]),
FILTER(
ALL('Table'),
'Table'[NameCode]='Table'[SubNameCode]
)
)
)
1pcsPrice =
IF(
NOT(ISBLANK(SELECTEDVALUE('Table'[Sub name]))),
DIVIDE(
[AllSubNamePrice],
[AllSubNameCount]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Analitika
Based on your description, you may create three measures as below.
AllSubNamePrice =
CALCULATE(
SUM('Table'[SubName price]),
FILTER(
'Table',
'Table'[NameCode]<>'Table'[SubNameCode]
)
)
AllSubNameCount =
IF(
SELECTEDVALUE('Table'[Sub name])<>"Salary",
CALCULATE(
SUM('Table'[Pcs]),
FILTER(
'Table',
'Table'[Name Price]<>'Table'[SubNameCode]
)
),
CALCULATE(
SUM('Table'[Pcs]),
FILTER(
ALL('Table'),
'Table'[NameCode]='Table'[SubNameCode]
)
)
)
1pcsPrice =
IF(
NOT(ISBLANK(SELECTEDVALUE('Table'[Sub name]))),
DIVIDE(
[AllSubNamePrice],
[AllSubNameCount]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank You, Works good, except finding PCS for Salary, always zeroes. But needed find value wich in column PCS with same date, but not All() need be in group by Name.
So must be something like:
If SubName = "Salary" Then take value from column PCS where 'Table'[NameCode]='Table'[SubNameCode] and same date
that because i need blanks too
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |