topic Re: Calculations on Summarized columns in Desktop
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024619#M760423
<P><LI-USER uid="316810"></LI-USER> Do you have a DimDate and DimCountry table? </P>
<P><A href="https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html" target="_blank">https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html</A> </P>
<P>I would suggest using the CALCULATE and ALL functions to get the total per date and country, as MEASURES, then just do a simple DIVIDE function to get the percent. </P>Thu, 19 Aug 2021 01:20:48 GMTAllisonKennedy2021-08-19T01:20:48ZCalculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024465#M760381
<P>Hello, </P><P> </P><P> 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.</P><P> </P><P>My original data is :</P><TABLE><TBODY><TR><TD>Call Date</TD><TD>Call Year</TD><TD>Call Month</TD><TD>Month Name</TD><TD>Month</TD><TD>PMN</TD><TD>MoU</TD><TD>MB</TD><TD>TMD.COUNTRY</TD><TD>Std Target</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBEM</TD><TD>0.083333</TD><TD>0</TD><TD>Albania</TD><TD>0%</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBVF</TD><TD>173.5167</TD><TD>1251.777</TD><TD>Albania</TD><TD>100%</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD>10.71667</TD><TD>0</TD><TD>Albania</TD><TD>0%</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBVF</TD><TD>623.0167</TD><TD>29903.94</TD><TD>Albania</TD><TD>100%</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>4935.924</TD><TD>Albania</TD><TD>0%</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>0</TD><TD>Albania</TD><TD>0%</TD><TD> </TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>0</TD><TD>Albania</TD><TD>0%</TD><TD> </TD></TR></TBODY></TABLE><P>There are more dates.</P><P> </P><P>I want is - 1) total of MB for each PMN of each Date. 2) Total of MB for each Country of each Date 3) Divide the TotalByCountry / TotalPerPMN and save it as a percentage.</P><P> </P><P>I started up with a Summarize table:</P><P> </P><P> </P><LI-CODE lang="javascript">Summary_MB =
SUMMARIZE(OutboundVolumes,
OutboundVolumes[Call Date],
OutboundVolumes[TMDPartners.COUNTRY],
OutboundVolumes[PMN (SYN)],
"TotalMBs", SUM(OutboundVolumes[Real Volume (MB)]),
"TotalMoUs", SUM(OutboundVolumes[Real MoU])
) </LI-CODE><P> </P><P>This gives me my (1) total of MB for each PMN of each Date</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="td_beginner_0-1629326399732.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/575731i0719FCE3E39E93E5/image-size/medium?v=v2&px=400" role="button" title="td_beginner_0-1629326399732.png" alt="td_beginner_0-1629326399732.png" /></span></P><P> </P><P>For Total for each Country by Date, I need to GroupBy CallDate, & Country, and SUM(TotalMBs). I couldn't figure out to do this part in the above Summarize command, so made another table (though wanted in the same table Summary_MB <span class="lia-unicode-emoji" title=":disappointed_face:">ðŸ˜ž</span> <span class="lia-unicode-emoji" title=":disappointed_face:">ðŸ˜ž</span></P><P> </P><LI-CODE lang="javascript">Summary_MB_Country =
SUMMARIZE(Summary_MB,
Summary_MB[Call Date],
Summary_MB[TMDPartners.COUNTRY],
"TotalMBsPerCountrty", SUM(Summary_MB[TotalMBs])
)</LI-CODE><P> </P><P>And this gives me perfect result:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="td_beginner_1-1629326708157.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/575733i49657BC7658B786D/image-size/medium?v=v2&px=400" role="button" title="td_beginner_1-1629326708157.png" alt="td_beginner_1-1629326708157.png" /></span></P><P> </P><P>I did get the sum of MB column for each PMN & Country based on Date - though they both are in different tables.</P><P> </P><P>Now, I got to divide </P><DIV><SPAN><SPAN>Summary_MB_Country[</SPAN></SPAN><SPAN>TotalMBsPerCountrty] </SPAN><SPAN><SPAN>/ </SPAN></SPAN><SPAN>Summary_MB[</SPAN><SPAN>TotalMBs] </SPAN></DIV><DIV><SPAN>for each country based on date and store the value in a column. </SPAN></DIV><P>Tried Addcolumn in the Summary_MB table, Add measure, but nothing gives the expected results. Expected result is :</P><TABLE><TBODY><TR><TD>Date</TD><TD>ALBAM</TD><TD>ALBEM</TD><TD>ALBVF</TD><TD>Grand Total</TD><TD>ALBAM %</TD><TD>ALBEM %</TD><TD>ALBVF %</TD></TR><TR><TD>1/1/2021</TD><TD>4936</TD><TD>0</TD><TD>31156</TD><TD>36091.64</TD><TD>14%</TD><TD>0%</TD><TD>86%</TD></TR></TBODY></TABLE><P> </P><P>Can anyone here help me figure out how do I accomplish this.</P><P>Any help is appreciated.</P><P> </P><P>Thank you</P>Wed, 18 Aug 2021 22:53:53 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024465#M760381td_beginner2021-08-18T22:53:53ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024619#M760423
<P><LI-USER uid="316810"></LI-USER> Do you have a DimDate and DimCountry table? </P>
<P><A href="https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html" target="_blank">https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html</A> </P>
<P>I would suggest using the CALCULATE and ALL functions to get the total per date and country, as MEASURES, then just do a simple DIVIDE function to get the percent. </P>Thu, 19 Aug 2021 01:20:48 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024619#M760423AllisonKennedy2021-08-19T01:20:48ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024806#M760465
<P>Hi,</P>
<P>Why are you creating a calculated table? A calculated table does not respond to a change in slicers? Why not write measures and drag those to your visual?</P>Thu, 19 Aug 2021 03:08:18 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2024806#M760465Ashish_Mathur2021-08-19T03:08:18ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2025666#M760679
<P><LI-USER uid="316810"></LI-USER> 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. </P>
<P> </P>
<P>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. </P>
<P> </P>
<P>Then I created a simple measure for Total MB:</P>
<P> </P>
<DIV>
<DIV><SPAN>Total MB = SUM(OutboundVolumes[MB])</SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN>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); </SPAN></DIV>
<DIV> </DIV>
<DIV>
<DIV><SPAN>Total MB all PMN = </SPAN></DIV>
<DIV><SPAN>IF([Total MB] <> BLANK(), </SPAN></DIV>
<DIV><SPAN>CALCULATE([Total MB], All(PMN))</SPAN></DIV>
<DIV><SPAN>)</SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN>Finally, I divided these: </SPAN></DIV>
<DIV> </DIV>
<DIV>
<DIV><SPAN>% of MB by Total MB all PMN = DIVIDE([Total MB], [Total MB all PMN])</SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN>Using the Matrix visual, you have your desired column: </SPAN></DIV>
<DIV> </DIV>
<DIV><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="AllisonKennedy_1-1629365967573.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/576177i06F62E90BF9E4086/image-size/medium?v=v2&px=400" role="button" title="AllisonKennedy_1-1629365967573.png" alt="AllisonKennedy_1-1629365967573.png" /></span>
<P> </P>
<BR />
<P> </P>
</DIV>
</DIV>
<DIV> </DIV>
</DIV>
</DIV>Thu, 19 Aug 2021 09:39:50 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2025666#M760679AllisonKennedy2021-08-19T09:39:50ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2027011#M761036
<P>wow, great <LI-USER uid="227497"></LI-USER> . Thanks a lot. </P><P>Your detailed explaination and pibx file help me achieve this big requirement (for me <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> ).</P><P>Your blog is also great, has so much detail. </P><P> </P><P>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.</P><P> </P><P>Thanks a lot. </P><P>I highly appreciate your guidance and support.</P><P> </P><P> </P>Thu, 19 Aug 2021 23:25:30 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2027011#M761036td_beginner2021-08-19T23:25:30ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2028967#M761665
<P><LI-USER uid="316810"></LI-USER> You're welcome. </P>
<P> </P>
<P>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? </P>
<P> </P>
<P>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. </P>
<P>This will then carry through to the formula.</P>Fri, 20 Aug 2021 23:19:02 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2028967#M761665AllisonKennedy2021-08-20T23:19:02ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2033368#M762696
<P>Hello <LI-USER uid="227497"></LI-USER> , </P><P> </P><P> There are few nulls in Country field, which I have filtered out. </P><P> 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.</P><P>Doing a Left Join, I have grabbed the Country and Std Target from the TMD into OutboundVolumes.</P><P> </P><P>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. </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Call Date</TD><TD>Call Year</TD><TD>Call Month</TD><TD>Month Name</TD><TD>Month</TD><TD>PMN</TD><TD>MoU</TD><TD>MB</TD><TD>TMD.COUNTRY</TD><TD>TMD.Std Target</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD><FONT color="#0000FF">ALBEM</FONT></TD><TD>0.083333</TD><TD>0</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBVF</TD><TD>173.5167</TD><TD>1251.777</TD><TD>Albania</TD><TD>100%</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD>10.71667</TD><TD>0</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBVF</TD><TD>623.0167</TD><TD>29903.94</TD><TD>Albania</TD><TD>100%</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>4935.924</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>0</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/1/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>0</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBVF</TD><TD>353.8</TD><TD>10586.44</TD><TD>Albania</TD><TD>100%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD>25.33333</TD><TD>12.27256</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBVF</TD><TD>314.6</TD><TD>24669.91</TD><TD>Albania</TD><TD>100%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>3019.622</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>147.5871</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>0</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>1/2/2021</TD><TD>2021</TD><TD>202101</TD><TD>Jan</TD><TD>1</TD><TD>ALBAM</TD><TD> </TD><TD>0</TD><TD>Albania</TD><TD>0%</TD></TR></TBODY></TABLE><P> </P><P>The Country & Std Target taken from TMD contains:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>PMN</TD><TD>Country</TD><TD>Target</TD></TR><TR><TD>ALBEM</TD><TD>Albania</TD><TD>0%</TD></TR><TR><TD>ALBVF</TD><TD>Albania</TD><TD>5%</TD></TR><TR><TD>ALBAM</TD><TD>Albania</TD><TD>95%</TD></TR></TBODY></TABLE><P> </P><P>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.</P><P> </P><P>Hope this helps.</P><P> </P><P>Thank You, </P><P> </P><P> </P>Mon, 23 Aug 2021 21:18:55 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2033368#M762696td_beginner2021-08-23T21:18:55ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2041541#M764703
<P><LI-USER uid="316810"></LI-USER> Can you please share a screenshot of your model view? You're on the right track with a Left Outer join - I'm just a bit lost with all the tables and abbreviations you mentioned, so need the model view to visualize what we're working with please. <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> </P>Thu, 26 Aug 2021 22:49:00 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2041541#M764703AllisonKennedy2021-08-26T22:49:00ZRe: Calculations on Summarized columns
https://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2041553#M764706
<P>Hello <LI-USER uid="227497"></LI-USER> </P><P> </P><P>My Model diagram:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="td_beginner_2-1630019656768.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/581638iB0DBA1966E1BB361/image-size/medium?v=v2&px=400" role="button" title="td_beginner_2-1630019656768.png" alt="td_beginner_2-1630019656768.png" /></span></P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="td_beginner_3-1630019696880.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/581639iDA295AA17A805C6F/image-size/medium?v=v2&px=400" role="button" title="td_beginner_3-1630019696880.png" alt="td_beginner_3-1630019696880.png" /></span></P><P> </P><P>By Creating the PMN table from TMD[PMN], I could atleast show all PMN in the dropdown list, but doesn't show in the chart or the Matrix.</P><P>3 PMN for Albania country are - ALBAM, ALBEM, and ALBVF</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="td_beginner_1-1630019157422.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/581637i3B4E216D3F77E3AD/image-size/medium?v=v2&px=400" role="button" title="td_beginner_1-1630019157422.png" alt="td_beginner_1-1630019157422.png" /></span></P><P> </P><P>Thank you</P>Thu, 26 Aug 2021 23:15:49 GMThttps://community.powerbi.com/t5/Desktop/Calculations-on-Summarized-columns/m-p/2041553#M764706td_beginner2021-08-26T23:15:49Z