Forum Discussion
Solved
Column to Measure
Hi Good day, I can anyone pls how to change the calculated column to measure, The highligthed column how to create in measure. Thank you Allan
Thank you for the help i got the solution by creating Dummy table.
Hi,
I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Rev sum: =
SUM( Sales[Revenue] )
Cumulate Rev sum: =
CALCULATE (
SUM ( Sales[Revenue] ),
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE ( ALL ( Sales ), Category[Category], 'Calendar'[Date] ),
ORDERBY ( 'Calendar'[Date], ASC ),
,
PARTITIONBY ( Category[Category] )
)
)
Category Rev total: =
IF (
NOT ISBLANK ( [Rev sum:] ),
CALCULATE ( [Rev sum:], ALL ( 'Calendar'[Date] ) )
)
% cumulate rev: =
VAR _cumulaterev =
CALCULATE (
SUM ( Sales[Revenue] ),
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE ( ALL ( Sales ), Category[Category], 'Calendar'[Date] ),
ORDERBY ( 'Calendar'[Date], ASC ),
,
PARTITIONBY ( Category[Category] )
)
)
VAR _categorytotalrev =
CALCULATE (
SUM ( Sales[Revenue] ),
WINDOW (
1,
ABS,
-1,
ABS,
SUMMARIZE ( ALL ( Sales ), Category[Category], 'Calendar'[Date] ),
ORDERBY ( 'Calendar'[Date], ASC ),
,
PARTITIONBY ( Category[Category] )
)
)
RETURN
DIVIDE ( _cumulaterev, _categorytotalrev )
Hi Jihwan Kim,
Thank you for your reply, but when i try the dax function show some error. can you pls help me to figure out.
Hi,
Thank you for your message. I can see that there is many-to-many relationship. If many-to-many relationship is not avoidable, the approach has to be done in a different way.
Please share your sample pbix file, and then I can try to look into it.
Thanks.
Thank you for the help i got the solution by creating Dummy table.