Getting blank value when dividing two measures in the matrix visual

[DSPVarma]

Hi All,

Please help me on the following;

I have 2 measures:

1. The first measure name is ‘Total Difference in minutes’ which is calculated by taking out the difference between the End date and the starting date (these 2 dates and times are customized dates that the user can enter).
   

    

2. The Second measure name is ‘Avg mins per booking’ which is calculated by dividing
   43200/Booking Count in the matrix visual, where 43200 is fixed.
   
   

    

The above 2 measures are showing correctly.

But when I am writing a following measure in matrix visual, I am getting blank value.

       Projected Bookings = DIVIDE ([Total Difference in Minutes],[Avg mins per Booking])
I am actually expecting the following result which I did in Excel 

 

 

[DSPVarma]

I tried all the methods but was unable to tackle this one. Can somebody help me out with this one?

[v-zhengdxu-msft · Accepted solution]

Hi @DSPVarma 

Thanks for the reply from @TomMartens , please allow me to provide another insight:

Copy the Slicer from Page FundOpt and Paste in page Fund workings:

Then select Sync:

View > Sync slicers:

Select the slicers and hide them in Page Fund workings:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DSPVarma]

@v-zhengdxu-msft Can you please help me with a measure to get the cumulative total for the "Projected fund required" column after sorting the "Sum of Net Contribution" column from highest to lowest. Many thanks for considering my request.

[DSPVarma]

@v-zhengdxu-msft Thank you very much. It worked for me with your solution.
@TomMartens Thanks a lot

[TomMartens]

Hey @DSPVarma ,

it's tough for me to provide guidance because I miss some information. In your listing number one and two you mentioned two measures:

• Total Difference in minutes
• Avg mins per bookin

All your screenshots do not show that "Total Difference in minutes' is properly working.

Consider creating a pbix file that contains sample data but still reflects your semantic model (tables, relationships, measures, and calculated columns). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link here in the public chat. Make sure that the file can be downloaded without authentication. If you are using a spreadsheet to import the the sample data from instead of the manual input method, share the spreadsheet as well.

Do not forget to explain the expected result based on the sample data you provide.

Regards,

Tom

[DSPVarma]

Hi @TomMartens 

Please find the google drive link as requested.

https://drive.google.com/drive/folders/1RORD2xai1rvH-No8-xoKZ1qi-cA9AeB4?usp=sharing

My requirement is filled in yellow color in the excel sheets.
 I want to replicate the same in the Power BI Matrix Visual.
In the Sample I have taken only one dimension (PC Column), but there are few others as well

[TomMartens]

Hey @DSPVarma ,

the blank values are due to the calculation of the measure
[Total Difference in Minutes] that is based on the measures
[Difference] and [Difference_Hours],
as soon a Start Date and End Date are selected, see the below screenshot taken from the report page "Fund workings". I added a slicer for the PC column only for simplifying the report.

Simple as that, because all the tables are not related you have to find a solution to provide at least a valid "Difference", that is not 0, what happens if nothing is selected becasue the End-Date and the Start-Date are the same.

Hopefully, this provides an idea of how to tackle your challenge.

Regards,

Tom