Forum Discussion

jkaemmerling's avatar
jkaemmerling
Frequent Visitor
5 years ago
Solved

Calculating category share by month

Hi there,   I apologize if I misuse terms or am unaware of a feature availabe in DAX/Power BI as I suspect my issue may be additional data prep, but I'm completely stumped. I've linked the data fil...
Need Help
  • jkaemmerling's avatar
    jkaemmerling
    5 years ago

    Hi all,

     

    aj1973  was very close with the solution, but a friend and I worked it out to be the following:

    DIVIDE(SUM(Combined[Count]),CALCULATE(SUM(Combined[Count]),ALL(Combined[Device])))
     
    This can only work when there is a relationship between the date table and main data set ('Combined', in this case), because using the ALL function as a filter, allows you to keep the denominator (total device count in a given month) the same. 
jkaemmerling's avatar
jkaemmerling
Frequent Visitor
5 years ago

Hi Amine,

 

Apologies, I'm not doing a good job explaining myself. My goal would for the chart to look like the below but have the %'s add up to 100% for each month.

 

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

Is this close?

 

  • jkaemmerling's avatar
    jkaemmerling
    Frequent Visitor
    5 years ago

    Yes that's the level of aggregraiton I need - but is it impossible to get it into a line chart?

    Or do I have to create a secondary table (like the one you did) in Transform Data to feed these values? 

      • jkaemmerling's avatar
        jkaemmerling
        Frequent Visitor
        5 years ago

        Thank you very much for your help. Your contribution has sparked the solution I ultimately went with. 

         

        A summary table needs to be created that (as you've shown) contains the device proportions out of each month and device. I thought PowerBI would have done that automatically, but I ended up creating the additional columns required in excel and re-uploading.

         

        I'm sure there's a way to do it in Power BI but I'm not skilled enough. Thank you!