Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Showing values in one dataset missing from another dataset on pie chart

I have 3 datasets.

  • Dataset A --> this is the “master” excel file with SenderName, SenderID, ShipmentNo. This represents total shipments.
  • Dataset B --> this is a sql server query which I merged with Dataset A to pull ShipmentNo’s in the database included in Dataset A. Not all shipments in Dataset A are included in Dataset B.
  • Organization table --> this is a database table which is connected to the SenderId in Dataset A and SenderOrganizationID in Dataset B

The purpose of this pie chart is to show the proportion of distinct ShipmentNo’s in Dataset A that is not included in Dataset B. In the pie chart below, there are 40 total shipments under that specific organization, and 39 of those shipments are missing, meaning not included in Dataset B. Therefore, the red part should cover most of the pie chart, not split in half like below.

The values in the pie chart at the moment are a result of measures I had initially created:

 

Total Shipments  = DISTINCTCOUNT('Dataset A'[ShipmentNo])

Shipmentcount = DISTINCTCOUNT(Milestones[ShipmentNo])

Missing Shipments = [Total Shipments] - [Shipmentcount]

 

 

I realized that merely finding the difference in the distinct count between both datasets will not solve my problem. However, I am stuck because I’m also unable to create a direct relationship between Dataset A & B. If anyone can help me find a workaround, it would be greatly appreciated!

Need Help

6 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you share a dummy pbix file which can reproduce the issue, so that we can better assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-ljerr-msft, 

       

      I greatly appreciate your help since I am still stuck on this. To simplify it, I am basically trying to show the measure "CountFalse" as a proportion of the "Total Shipments" measure in the pie chart. In the pbix file, I'm trying to get the pie chart to read: "39 out of the 40 distinct shipments are false under organization TOL." Therefore, the red portion should cover 97.5% of the pie when sliced by TOL. 

       

       

       

      Here is the link to the file on one drive: https://1drv.ms/u/s!Ao2_nHZB2FWRbxLiiIbwp0iax9w

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Anonymous

         

        Hi

         

        Put in Legend Your Matched Column

         

        And In Values Total Shipment

         

         

        Regards

         

        Victor