Forum Discussion

abhifx's avatar
abhifx
Frequent Visitor
2 years ago

Unable to Replace Column data in Matrix Using Switch

Dear All   I have been trying to replicate the revenue statement in Power BI directly linked with a trial balance. However once i insert a format and link multi level data into Power BI, I am unabl...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi abhifx

Have you tried checking whether it is really Total Sale or maybe there spaces before or after or there are more than one spaces between Total and Sale?
You can upload your pbix to Google or One Drive and share a public link.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi abhifx ,

     

    It returns blank for the second row because [Total Sales] is blank.

     

    • abhifx's avatar
      abhifx
      Frequent Visitor
      2 years ago

      I might have responded against the wrong reply. This is actually part of the problem as the total sales also get distributed among the sales heads. how to ignore the sub head and add the total? I also need the same to get percentage of each head against sales

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        Hi abhifx ,

         

        The problem is that MIS Form flows to a blank category in Database for MIS which of course doesn't have an equivalent in Trial. You can't expect Power BI to know which accounts in Trial should be aggregated for Total Sale.

        This measure returns a value only if Line Item Desc is SALE, not Total Sale

        Total Sales =
CALCULATE (
    SUM ( Trial[Value in Lac] ),
    'MIS Format'[Line Item Desc] = "SALE" 
)

        You can change this behaviour by using FILTER and wrapping 'MIS Format' in ALL. Your new measure would be:

        Total Sales =
CALCULATE (
    SUM ( Trial[Value in Lac] ),
    FILTER ( ALL ( 'MIS Format' ), 'MIS Format'[Line Item Desc] = "SALE" )
)

         

        This wil be tedious as you'll have to do that for all measures that return blank when they shouldn't. Alternatively, you can modify MIS Format so all category items that should be included in each Line Item Desc has its own line. For example, Total Int and Dept would have a line for Fin Charges and another one for Depreciation.