Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I
im new in pbi
i need to define a new table in pbi model with this data:
i use summarize to define the first 3 columns but i dont know how to calculate the 4th column ("cumulated")
thanks falks!
Solved! Go to Solution.
Hi @rufmau68
I think your CMSCardinis Table is the same as below.
Then you build a summarize table :SparklineTable_Debug.
SparklineTable_Debug =
SUMMARIZE(CMSCardinis, CMSCardinis[Codice WBS], CMSCardinis[Mese di Rendicontazione].[Date], "Y", sum(CMSCardinis[Giornate]))
To calculate the running total of Y for each Codice WBS, you could build a measure like amitchandak's reply, or you could build a calculated column.
Measure:
Basic Measure =
CALCULATE (
SUM ( [Y] ),
FILTER (
ALL ( SparklineTable_Debug ),
SparklineTable_Debug[Codice WBS] = MAX ( SparklineTable_Debug[Codice WBS] )
&& SparklineTable_Debug[Date] <= MAX ( SparklineTable_Debug[Date] )
)
)
Measure_Y_cumulato = SUMX(SparklineTable_Debug,[Basic Measure])
Basic will show wrong Total in table visual, so we need to build a new one basiced on Basic Measure.
Or you could build a calculated column in SparklineTable_Debug Table.
Column_Y_cumulato =
CALCULATE (
SUM ( [Y] ),
FILTER (
SparklineTable_Debug,
SparklineTable_Debug[Codice WBS] = EARLIER ( SparklineTable_Debug[Codice WBS] )
&& SparklineTable_Debug[Date] <= EARLIER ( SparklineTable_Debug[Date] )
)
)
Result:
You can download the pbix file from this link: create table with cumulative measures
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i try it,
but dont run
when i use sumethinks like
if i change formula like this, Y_cumulato = Y grand total
Hi @rufmau68
I think your CMSCardinis Table is the same as below.
Then you build a summarize table :SparklineTable_Debug.
SparklineTable_Debug =
SUMMARIZE(CMSCardinis, CMSCardinis[Codice WBS], CMSCardinis[Mese di Rendicontazione].[Date], "Y", sum(CMSCardinis[Giornate]))
To calculate the running total of Y for each Codice WBS, you could build a measure like amitchandak's reply, or you could build a calculated column.
Measure:
Basic Measure =
CALCULATE (
SUM ( [Y] ),
FILTER (
ALL ( SparklineTable_Debug ),
SparklineTable_Debug[Codice WBS] = MAX ( SparklineTable_Debug[Codice WBS] )
&& SparklineTable_Debug[Date] <= MAX ( SparklineTable_Debug[Date] )
)
)
Measure_Y_cumulato = SUMX(SparklineTable_Debug,[Basic Measure])
Basic will show wrong Total in table visual, so we need to build a new one basiced on Basic Measure.
Or you could build a calculated column in SparklineTable_Debug Table.
Column_Y_cumulato =
CALCULATE (
SUM ( [Y] ),
FILTER (
SparklineTable_Debug,
SparklineTable_Debug[Codice WBS] = EARLIER ( SparklineTable_Debug[Codice WBS] )
&& SparklineTable_Debug[Date] <= EARLIER ( SparklineTable_Debug[Date] )
)
)
Result:
You can download the pbix file from this link: create table with cumulative measures
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@rufmau68 , Assume you measure is sum measure
Something like sum(Table[Some value])
Try a new measure for Cumulative
calculate([measure], filter(allselected(Table]), table[category] = max(Table[category]) && table[data] <= max(Table[data])))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |