Need assistance with Dax measurement

[BlackCat]

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 header

[v-kelly-msft · Accepted solution]

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!

[amitchandak]

@BlackCat ,

not very clear. But conditional formatting can help

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

https://exceleratorbi.com.au/dax-unichar-function-power-bi/

[BlackCat]

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.

[v-kelly-msft]

Hi @BlackCat ,

You can create measures or calculated columns to realize it.

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[BlackCat]

@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?

[BlackCat]

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?

[v-kelly-msft · Accepted solution]

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!

[BlackCat]

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")

)

[BlackCat]

@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 fields

[v-kelly-msft]

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!

[BlackCat · Accepted 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.