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
BlackCat
Helper III
Helper III

Need assistance with Dax measurement

I have created this report in Power BI with all the headers below. I have to add the highlighted fields to the reports but first create the measurement. I am new to Power BI and I am having difficulty writing the measurements, any assistance is greatly appreciated. @Fowmy 

 

Calculation for highlighted headerCalculation for highlighted header

2 ACCEPTED SOLUTIONS

Hi  @BlackCat ,

 

See the measures below:

Total GM$ = MAX('Table'[Sum of Total Contract Value])*MAX('Table'[GM %])/100
Contact value/Mo = DIVIDE(MAX('Table'[Sum of Total Contract Value]),MAX('Table'[Contract Duration in Months]))
GM/MO = DIVIDE('Measurements'[Total GM$],MAX('Table'[Contract Duration in Months]))
Weighted contract/mo = DIVIDE('Measurements'[weighted total contract],MAX('Table'[Contract Duration in Months]))
Weighted GM$/mo = DIVIDE('Measurements'[Weighted total GM$],MAX('Table'[Contract Duration in Months]))
weighted total contract = MAX('Table'[Sum of Total Contract Value])*MAX('Table'[Deal Stage %])
Weighted total GM$ = DIVIDE('Measurements'[Total GM$],MAX('Table'[Deal Stage %]))

And calcualted columns are as below:

_Col-Contact value/Mo = DIVIDE('Table'[Sum of Total Contract Value],'Table'[Contract Duration in Months])
_Col-GM/MO = DIVIDE('Table'[_col-Total GM$],'Table'[Contract Duration in Months])
_col-Total GM$ = 'Table'[Sum of Total Contract Value]*'Table'[GM %]/100
_Col-Weighted contract/mo = DIVIDE('Table'[_Col-weighted total contract],'Table'[Contract Duration in Months])
_Col-Weighted GM$/mo = DIVIDE('Table'[_Col-Weighted total GM$],'Table'[Contract Duration in Months])
_Col-weighted total contract = 'Table'[Sum of Total Contract Value]*'Table'[Deal Stage %]
_Col-Weighted total GM$ = DIVIDE('Table'[_col-Total GM$],'Table'[Deal Stage %])

My .pbix file has been updated,try to open again.

If the notification still pops out,click"close",you can still open my file.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

View solution in original post

Thank you Kelly, this has helped me immensly. I now understand how to achieve the measurmements required and translate from formulas in Excel to Power Bi.

View solution in original post

10 REPLIES 10

Although this i'm sure will be useful in the future, color coding is not what I am asking for... I highlighted the colums with the Excel calculation, I must translate that calculation into a PowerBI measurement and add it to the report. Thank you for your suggestions, any other feedback is welcome.

Hi @BlackCat ,

 

You can create measures or calculated columns to realize it.

And you will see:

v-kelly-msft_0-1607665217893.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

@v-kelly-msft Hi Kelly, the totals are not adding up, they are giving me to the weighted month over month revenue for the total, and I adjusted in format but does not return a total? Any suggestions?Totals not adding.png

Hi Kelly,

Unfortunately, I cannot open the file. I am on the latest version (November 2020) but still won't let me access your pbix file. It looks like what I am looking for, can you screenshot the measurements?POwer BI.png

Hi  @BlackCat ,

 

See the measures below:

Total GM$ = MAX('Table'[Sum of Total Contract Value])*MAX('Table'[GM %])/100
Contact value/Mo = DIVIDE(MAX('Table'[Sum of Total Contract Value]),MAX('Table'[Contract Duration in Months]))
GM/MO = DIVIDE('Measurements'[Total GM$],MAX('Table'[Contract Duration in Months]))
Weighted contract/mo = DIVIDE('Measurements'[weighted total contract],MAX('Table'[Contract Duration in Months]))
Weighted GM$/mo = DIVIDE('Measurements'[Weighted total GM$],MAX('Table'[Contract Duration in Months]))
weighted total contract = MAX('Table'[Sum of Total Contract Value])*MAX('Table'[Deal Stage %])
Weighted total GM$ = DIVIDE('Measurements'[Total GM$],MAX('Table'[Deal Stage %]))

And calcualted columns are as below:

_Col-Contact value/Mo = DIVIDE('Table'[Sum of Total Contract Value],'Table'[Contract Duration in Months])
_Col-GM/MO = DIVIDE('Table'[_col-Total GM$],'Table'[Contract Duration in Months])
_col-Total GM$ = 'Table'[Sum of Total Contract Value]*'Table'[GM %]/100
_Col-Weighted contract/mo = DIVIDE('Table'[_Col-weighted total contract],'Table'[Contract Duration in Months])
_Col-Weighted GM$/mo = DIVIDE('Table'[_Col-Weighted total GM$],'Table'[Contract Duration in Months])
_Col-weighted total contract = 'Table'[Sum of Total Contract Value]*'Table'[Deal Stage %]
_Col-Weighted total GM$ = DIVIDE('Table'[_col-Total GM$],'Table'[Deal Stage %])

My .pbix file has been updated,try to open again.

If the notification still pops out,click"close",you can still open my file.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

Hi @v-kelly-msft because your solution has been successful, I have one more question.... I am trying to total my weighted contract/mo and weighted GM$/mo in the middle -Revenue- visual in my weighted report, but it is only giving me the same total as month over month and not the actual total, where as in my "Standard" report I am getting the total at the end of every row. 

Measurements for weighted visualization is: 

Weighted GM$/mo = DIVIDE('Deal'[Weighted total GM$],MAX('Deal'[Contract Duration in Months]))
Weighted contract/mo = DIVIDE('Deal'[weighted total contract],MAX('Deal'[Contract Duration in Months]))
Revenue Calendar = ADDCOLUMNS(
CALENDAR(STARTOFMONTH(FIRSTDATE(Deal[Contract Start])), ENDOFMONTH(LASTDATE(Deal[Contract End])))
, "Month", FORMAT([Date], "yyyy-MM")
, "Month Year", FORMAT([Date], "MMM yyyy")
)
Standard for the same visualization is: 
Revenue =
ROUND(
CALCULATE(
SUMX(Deal, [Contract Value per Month] * [Selected Months] /*(DATEDIFF(MAX(MIN('Revenue Calendar'[Date]),[Contract Start]), MIN(MAX('Revenue Calendar'[Date]),[Contract End]), MONTH) + 1) */ )
, FILTER(Deal
, Deal[Contract Start] <= MAX('Revenue Calendar'[Date]) && Deal[Contract End] >= MIN('Revenue Calendar'[Date])
)
)
, 0
)
Revenue Calendar = ADDCOLUMNS(
CALENDAR(STARTOFMONTH(FIRSTDATE(Deal[Contract Start])), ENDOFMONTH(LASTDATE(Deal[Contract End])))
, "Month", FORMAT([Date], "yyyy-MM")
, "Month Year", FORMAT([Date], "MMM yyyy")
)Standard.pngWeighted.png

@v-kelly-msft Hi Kelly, everything but the weighted measurements work, thank you. The measurements "weighted total contract = MAX('Deal'[Sum of Total Contract Value])*MAX('Deal'[Opportunity Status])" & Calculated column "Col-weighted total contract = 'Deal'[Sum of Total Contract Value]*'Deal'[Opportunity Status)" is where I am erroring out. For the measurement I am getting this error: "Calculation error in measure 'Deal'[weighted total contract]: The function SUM cannot work with values of type String". I have attached a screenshot of the desktop visual I am working on so you can see the fields I am using. Deal Stage % is equivalent to Opportunity Status, apologies I confused this field. Any assistance on the weighted is very much appreciated. Almost there!Weighted Average report with Associated fieldsWeighted Average report with Associated fields

Hi  @BlackCat ,

 

For error: The function SUM cannot work with values of type String,it means that the format of the field is text,change it to decimal number can fix it.

Check the screenshot attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Thank you Kelly, this has helped me immensly. I now understand how to achieve the measurmements required and translate from formulas in Excel to Power Bi.

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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