Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculations on Summarized columns

Hello,         Reading/Watching articles, questions, I learnt about Summarize and found it appropriate for my usage. Am stuck at a point. Hope some one could help me.   My original data is : ...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    Anonymous  I see no one has come back with a more detailed reply and I have a bit more time now, so please see attached pbix file below my signature. 

     

    I have created Dimension tables for your for Date, Country and PMN as this will make your life easier in the long run. I referenced the OutboundVolumes table to generate these (see my video on survey data for something similar) but ideally you'd have this in your data source already and can pull into Power BI. 

     

    Then I created a simple measure for Total MB:

     

    Total MB = SUM(OutboundVolumes[MB])
     
    Then I created a measure to get Total MB for ALL PMN (when you configure the visual correctly this will give you the total per Country and Date); 
     
    Total MB all PMN =
    IF([Total MB] <> BLANK(),
    CALCULATE([Total MB], All(PMN))
    )
     
    Finally, I divided these: 
     
    % of MB by Total MB all PMN = DIVIDE([Total MB], [Total MB all PMN])
     
    Using the Matrix visual, you have your desired column: 
     

     


     

     
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous  I see no one has come back with a more detailed reply and I have a bit more time now, so please see attached pbix file below my signature. 

 

I have created Dimension tables for your for Date, Country and PMN as this will make your life easier in the long run. I referenced the OutboundVolumes table to generate these (see my video on survey data for something similar) but ideally you'd have this in your data source already and can pull into Power BI. 

 

Then I created a simple measure for Total MB:

 

Total MB = SUM(OutboundVolumes[MB])
 
Then I created a measure to get Total MB for ALL PMN (when you configure the visual correctly this will give you the total per Country and Date); 
 
Total MB all PMN =
IF([Total MB] <> BLANK(),
CALCULATE([Total MB], All(PMN))
)
 
Finally, I divided these: 
 
% of MB by Total MB all PMN = DIVIDE([Total MB], [Total MB all PMN])
 
Using the Matrix visual, you have your desired column: 
 

 


 

 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    wow, great AllisonKennedy . Thanks a lot. 

    Your detailed explaination and pibx file help me achieve this big requirement (for me 🙂 ).

    Your blog  is also great, has so much detail. 

     

    I want to show the PMNs with 0 Total MB/Total MB all PMN of a Country. I tried the IF clauses, but that brings up all the PMNs. Any way that we can show up 0% for any of the Total is 0 or blank.

     

    Thanks a lot. 

    I highly appreciate your guidance and support.

     

     

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous You're welcome. 

       

      In order to show 0% you will either get all PMNs displayed as you have already discovered, or you need to specify which PMNs you want to see somehow. Can you explain this in further detail? 

       

      Other option would be to use Power Query - so click 'Transform Data' to open Power Query, then in the Tranform tab > Replace values. Replace null with 0. 

      This will then carry through to the formula.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello AllisonKennedy , 

         

             There are few nulls in Country field, which I have filtered out. 

              The see the issue - the OutboundVolume is merged with TMD table that gives Country. TMD contains all PMNs which may not be in the OutboundVolumes. Those PMNs, even if merged with OutboundVolumes, would contain no data for Dates, MB, or any relevant field. On by filtering Date, dataset could not have those PMNs, as the value for CallDate is null.

        Doing a Left Join, I have grabbed the Country and Std Target from the TMD into OutboundVolumes.

         

        It's like, "ALBEM" is for 1/1/21, but not for 1/2/21, so for 1/2/21, it doesn't show ALBEM. 

        Call DateCall YearCall MonthMonth NameMonthPMNMoUMBTMD.COUNTRYTMD.Std Target
        1/1/20212021202101Jan1ALBEM0.0833330Albania0%
        1/1/20212021202101Jan1ALBVF173.51671251.777Albania100%
        1/1/20212021202101Jan1ALBAM10.716670Albania0%
        1/1/20212021202101Jan1ALBVF623.016729903.94Albania100%
        1/1/20212021202101Jan1ALBAM 4935.924Albania0%
        1/1/20212021202101Jan1ALBAM 0Albania0%
        1/1/20212021202101Jan1ALBAM 0Albania0%
        1/2/20212021202101Jan1ALBVF353.810586.44Albania100%
        1/2/20212021202101Jan1ALBAM25.3333312.27256Albania0%
        1/2/20212021202101Jan1ALBVF314.624669.91Albania100%
        1/2/20212021202101Jan1ALBAM 3019.622Albania0%
        1/2/20212021202101Jan1ALBAM 147.5871Albania0%
        1/2/20212021202101Jan1ALBAM 0Albania0%
        1/2/20212021202101Jan1ALBAM 0Albania0%

          

        The Country & Std Target taken from TMD contains:

        PMNCountryTarget
        ALBEMAlbania0%
        ALBVFAlbania5%
        ALBAMAlbania95%

         

        Could we possibly show all the partners from the TMD for each date regardless of the data being available in the OutboundVolumes. If there is no data, then we show 0.

         

        Hope this helps.

         

        Thank You,